home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Cream of the Crop 1
/
Cream of the Crop 1.iso
/
PROGRAM
/
DATA_BSE.ARJ
/
CRDE.DOC
next >
Wrap
Text File
|
1991-01-05
|
305KB
|
10,880 lines
The
C
Relational
Database
Engine
Copyright 1990, Mark Lang.
Table of Contents
Chapter 1, Introduction ............................................... 1
Chapter 2, Getting Started ............................................ 3
System Requirements ............................................ 3
Program capacities ............................................. 3
Installing CRDE ................................................ 3
What comes with CRDE ........................................... 4
Using CRDE with Turbo C ........................................ 5
Linking CRDE ............................................ 5
Reserved function calls .................................. 5
Using CRDE in your program ............................... 5
Using disk caches with CRDE .............................. 6
CRDE tables .................................................... 6
Basic terminology .............................................. 7
Chapter 3, Creating Tables ............................................ 8
The TABLE type ................................................. 8
Table descriptors .............................................. 9
Defining a primary key ......................................... 10
Creating a database ............................................ 12
The kitchen sink approach ............................... 12
The relational approach ................................. 13
Chapter 4, Filling Tables with Information ............................ 14
Loading rows with tload ....................................... 14
Error -41 ...................................................... 15
Inserting rows ................................................. 16
Replacing rows ................................................. 17
Write modes .................................................... 17
Chapter 5, Basic Table I/O ............................................ 19
Opening tables ................................................. 19
Integrity checking ............................................. 19
Closing a table ............................................... 19
Checking table accessability ................................... 20
Renaming tables ................................................ 20
Erasing tables ................................................. 20
Secondarr indexes .............................................. 20
Creating indexes ........................................ 20
Index descriptors ....................................... 21
Primary index vs Secondary indexes ...................... 22
Rebuilding indexes ...................................... 23
Dropping indexes ........................................ 23
Redundant indexes ....................................... 23
Chapter 6, Viewing a Table ............................................ 24
Chapter 7, The Search-Related Functions ............................... 26
The seven search related functions ............................. 26
The search expression .......................................... 26
The search engine .............................................. 27
Search related operations ...................................... 27
Selecting rows .......................................... 27
Projecting rows ......................................... 28
Deleting rows ........................................... 29
Changing rows ........................................... 30
Scanning rows ........................................... 30
Looking up a row ........................................ 31
Getting rows into C variables ........................... 31
"..if" functions ............................................... 32
Chapter 8, Miscelleneous operations ................................... 36
Restructuring tables ........................................... 36
Renaming columns ............................................... 37
Getting info from your tables .................................. 38
Importing and Exporting data ................................... 39
Ascii files ............................................. 39
dBASE files ............................................. 41
Chapter 9, Working with Dates ......................................... 43
The date_t type and date math .................................. 43
Converting calender dates ...................................... 43
Miscelleneous date functions ................................... 44
Month and year math ............................................ 45
Day of week functions .......................................... 45
Chapter 10, Temporary Tables ......................................... 47
STATIC tables .................................................. 47
Virtual tables ................................................. 47
TEMP tables and auto-cleanup ................................... 48
Rules for using TEMP tables .................................... 49
Chapter 11, Performing Queries ........................................ 50
What is a query? ............................................... 50
Simple Queries ............................................... 52
Simple select queries ................................... 52
ORing queries ........................................... 53
Projecting columns ...................................... 54
Selecting distinct values ............................... 55
Joining tables .......................................... 55
Order By queries ........................................ 57
Statistical queries, making calculations ................ 58
Set-related queries ..................................... 59
Complex Queries ................................................ 60
Subqueries .............................................. 60
Correlated subqueries ................................... 60
Multi-table joins ....................................... 61
"In" subqueries ......................................... 62
Multiple subqueries ..................................... 62
Group By queries ........................................ 63
Writing grouping functions ....................... 64
Group Having queries ............................. 68
Set Group queries ....................................... 69
Optimizing Queries ............................................. 73
Chapter 12, Creating Reports .......................................... 74
Single table reports ........................................... 74
Multi-table reports ............................................ 75
Chapter 13, Maintaining Database Integrity ............................ 79
Entity integrity ............................................... 79
Domain integrity ............................................... 79
Referential integrity .......................................... 80
Foreign an primary keys ................................. 80
and inserting rows ...................................... 80
and deleting rows ....................................... 81
and changing rows ....................................... 82
Chapter 14, Performing Tansactions .................................... 84
CRDE's transaction tracking features ........................... 84
How does transaction tracking work? ............................ 85
Chapter 15, Reparing Tables ........................................... 86
Corrupted indexes .............................................. 86
Testing your indexes ........................................... 86
Repairing a table with trepair ................................. 86
Limitations of trepair ......................................... 87
Reference Guide ....................................................... 88
Standard Conventions ........................................... 88
Return values ........................................... 88
Functions which return tables ........................... 89
Parameters in CRDE functions ............................ 90
Auto-cleanup of TEMP tables ............................. 90
Function library ............................................... 92
Global Variables ...................................................... 170
Appendix A, Error Codes ............................................... 173
Chapter 1
Introduction
The C Relational Database Engine (called CRDE hereafter) is a library
of over 90 functions which support the creation, maintenance, and
querying of single-user databases. CRDE is not another B-tree library.
It goes far beyond the capabilities of most data management libraries.
Complex operations such as sorting, restructuring, and joining tables
are single line function calls in CRDE. In terms of ease of use,
relational querying power, and sheer speed, CRDE is on par with
professional relational database managent systems.
Below are listed just some of the features of CRDE:
CRDE uses a sophisticated file and memory manager to
manage tables and indexes. The manager intelligently
coordinates all your resources for maximum performance. It
also features support for transaction tracking, without a
network.
CRDE tables each have their own data dictionary which
contains information about the columns and indexes for a
table. Among other things, CRDE uses this information to
maintain and use indexes transparently.
CRDE can match the relational querying power of high level
query languages such as SQL, often in as little as one or two
function calls. However CRDE is not an SQL engine and
does not require a preprocessor.
CRDE requires no special initialization calls or setup, and is
not a TSR. Simply link the library and use the functions.
CRDE uses C data types and stores data as C structures,
making sharing data between C and CRDE tables very easy.
CRDE has a complete date library for managing dates and
performing date math.
CRDE introduces the TABLE datatype which is the heart of
CRDE. A TABLE is very similar to a FILE in terms of use
and design, although much more powerful. Functions can
accept tables as parameters and return tables as results.
CRDE comes with a view facility which you can integrate
directly into your programs. With it you can view any table
in any sorted order, selecting only those columns you wish to
display, in any window you want to view the table in, with a
single function call.
- 1 -
This manual assumes that you are a fairly experienced C programmer with
an understanding of the relational database model. Familiarity with a
high level relational database language and/or SQL is also recommended.
You should also be familiar with Turbo C 2.0 and how to link and use
library modules with your own programs.
- 2 -
Chapter 2
Getting Started
System Requirements
To use CRDE you must have all of the following:
- An IBM PC, XT, AT, PS/2 or 100% compatible computer.
- At least 512k of internal memory.
- A hard drive.
- DOS 2.0 or greater.
- Turbo C 2.0.
You should also make sure that your CONFIG.SYS file contains the statement
FILES = 20
in it. Although this setting is not mandetory, it is the recommended
setting for using CRDE for optimal performance.
Program Capacities
In CRDE, a single table may have up to
- 2 billion rows with up to 4000 bytes each.
- 256 columns with up to 256 characters each.
- 1 primary index composed of up to 8 columns.
- 8 secondary indexes composed of up to 8 columns each.
Installing CRDE
Before you can use CRDE it must be installed on your computer. CRDE is
not copy-protected, so installation is a fairly straight-forward.
Simply copy the contents of the CRDE diskette into your working
directory. For example, if your working directory is C:\TURBOC, place
the CRDE diskette into drive A: and type
C:>copy A:*.* C:\TURBOC
- 3 -
to copy CRDE into your hard drive. Specifically, Turbo C must be able to
access 3 files on your CRDE diskette, crde.lib, crde.h, and date.h.
What comes with CRDE
There are 18 files on the CRDE diskette. Only the first three are
required to create and compile applications with CRDE. The rest are
sample programs which you may run and examine at your convenience.
crde.lib This is the CRDE library. You must link this to any
application which uses CRDE.
crde.h This header file contains prototypes of the bulk of CRDE
functions plus several pre-defined macros used by CRDE.
Include this in any source file which uses CRDE functions.
date.h This header file contains prototypes for all the date
functions in the CRDE library. It also defines the
date_t type used by CRDE. Include this in any source
file which uses any of CRDE's date functions or the
date_t datatype.
read.me Ascii file containing last minute information about
bug-fixes or updates not found in the CRDE manual which
can be loaded into virtually any editor for inspection.
Your CRDE diskette may or may not contain this file.
sample.exe The sample application provided with CRDE which
illustrates some of CRDE's features. sample.exe
maintains a small, fully-functional, library database.
sample.c Source code to the sample application.
sample.prj Project file for creating the sample application.
tview.c Program written using CRDE functions to view any CRDE
table from the command line.
tstruct.c Program written using CRDE functions to view any CRDE
table's structure from the command line.
texport.c Program written using CRDE functions to export any CRDE
table to ascii or dBASE format.
- 4 -
Using CRDE with Turbo C
Linking CRDE
To use CRDE include the crde.h header file in any source file containing
CRDE function calls. You may also need to include the date.h file if
you use any of CRDE's date functions. Programs written using CRDE
functions must be compiled in the LARGE memory model and linked to the
CRDE library crde.lib. To do this from the integrated environment you
must create a project file to let Turbo C know that you want it to link
the CRDE library with your program. An example project file to create
an executable file for a program "noname.c" might look like the following:
noname
crde.lib
See your Turbo C user's manual for more information on creating project
files. You can also link you program from the command line using TLINK.
To create an executable file using TLINK for the same program "noname.c"
would look something like this:
tlink noname.obj c0l.obj,,, emu.lib mathl.lib cl.lib crde.lib
It is also a good idea when compiling programs using CRDE to set the
"Options\Linker\Warn duplicate symbols" On. This will keep you from
accidentally duplicating a CRDE function within your own program and
causing a program crash.
Reserved function calls
The CRDE library is made up of a central engine TENGINE, plus object
code for each CRDE function. TENGINE consists of 100 "engine calls"
which are used reserved specifically for use by CRDE functions. The
CRDE engine calls have the format _CRDE_XX where XX is a number from
00 to 99. Calling any of these routines directly will most likely
cause your program to hang.
Using CRDE in your program
CRDE requires no special initialization or setup. Simply link the
library and use the functions. However, every program which uses CRDE
must contain and assign the global variable tbuffers. This variable
tells CRDE how much memory to set aside for its exclusive use. CRDE
allocates memory in 512 byte blocks or buffers. For example, the
following would set aside 64k of memory for use by CRDE's functions:
int tbuffers = 128;
int main()
{
/* program here */
}
- 5 -
tbuffers must be declared globally. CRDE requires the tbuffers
variable to run. Fortunately, the linker will tell you if you forget
to declare tbuffers.
Assigning a good value to tbuffers depends upon many factors: how much
memory is avaiable, the number and sizes of your tables, how much memory
your program needs, if you do large transactions on tables, etc. A
good minimal value for most programs is 128 buffers. This value
provides solid performance for most operations and only uses 64k of
memory. For optimal performance in programs which are database
intensive or make use of CRDE's transaction tracking facilities should
consider a setting of 256 or 512 buffers or more if memory allows.
CRDE requires a minium of at least 64 buffers (32k) to run efectively.
Note: CRDE allocates its buffers according to tbuffers automatically
after the first table is opened or created. Changing the value of
tbuffers after that will not effect the amount of buffers used by CRDE.
Using disk caches with CRDE
Do not use disk caches, heap expanders, or similar types of programs in
conjunction with CRDE. CRDE has a sophisticated built-in file and memory
manager which has all the capabilities of the above programs and more.
CRDE knows about which tables and indexes will involved in an operation
while these other program do not. Using them will interfere with CRDE
and will probably impede performance rather than improve it.
CRDE tables
A CRDE table consists of one or more files. Specifically, a table can
consist of the table itself, a primary index, and up to 8 secondary
indexes. Although each of these objects consists of a separate file on
disk, CRDE considers them to be a unit.
On disk, all the objects related to a single table have the same base
name as the table and are distinguished from each other by their
filename extension.
Extension Type of Object
.t table
.p primary index
.sX secondary index
The X in a secondary indexes extension stands for a digit from 0 to 7.
A table's base name may be any legal DOS filename, but may not include
wildcards or an extension.
- 6 -
Basic Terminology
The following is a set of basic terms you should be familiar with before
using CRDE.
database A database is a collection of information or data
pertaining to a specific project. For instance, a
library database would contain all the information
about books, members, loans, etc. in the library. A
relational database consists of one or more tables.
table A table is a discrete set of information within a
database. A table can be visualized as a two
dimensional chart with rows and columns. In C, a
table might be more analogous to an array of
structures. CRDE also considers any indexes created
on the table to be part of the table.
row A row is a horizontal slice of a table. In C, a row is
similar to an item in an array. The terms row and
record are interchangeable in CRDE.
column A column is a vertical slice of a table. In C, a
column is similar to a single field in a structure.
The terms column and field are interchangeable in CRDE.
index An index is a special file organized for quick access
to information in a table. An index on a table is
similar to an index in a book. CRDE can use an index
to lookup information very quickly.
key A key describes the columns which make up an index. In
CRDE, up to 8 columns can be part of a key.
- 7 -
Chapter 3
Creating Tables
The TABLE type
CRDE introduces a new data type, TABLE, which is much akin to the FILE
type defined in the C standard library, although much more powerful,
as you shall see. Tables can be assigned to variables, passed as
parameters, and returned by functions. This object-oriented approach
to data management, including the casual way in which you can create
and manipulate tables, is at the heart of CRDE's power. You can
declare variable as TABLE * just as you would a FILE *. For example,
to declare variable t as a TABLE would look like the following:
TABLE *t;
Note: you cannot declare a variable to be of type TABLE. It must be
a TABLE *. Doing so will cause a compilation error.
You can create a new table with the tcreat function. The following
program creates a simple table called "sups" containing 4 columns: s#,
sname, city, status, and assigns it to t.
#include <stdio.h>
#include "crde.h"
int main()
{
TABLE *t;
t = tcreat("customer", "s# i, sname c26, city c26, status i");
tclose(t);
}
tcreat is declared in crde.h as:
TABLE *tcreat(char *name, char *td);
The first parameter name is the name of the table. name must be a legal
DOS pathname <= 64 characters. It may not contain wildcards or an
extension.
- 8 -
Table descriptors
The second parameter td is known as the table descriptor. The table
descriptor defines the columns in the table, their names, types and
sizes. A table descriptor should be an asciiz string of the format:
"colname1 coltype1 colname2 coltype2 colname3 coltype3 ..."
Column names can consist of any characters except whitespace. White-
space characters are all characters classified by the standard library
function isspace() plus the comma ','. Thus column names can contain
letters, numbers, and other characters like '#' and '-'. However, you
should avoid using the '*' and '!' characters in column names since
they have special meaning in CRDE. A table may contain up to 256
columns.
A column name should describe the contents of the column. A table may
not contain two columns with the same name. Column names are case-
sensitive however, so "Name", "NAME", and "name" are all considered
different. Every column name in a table descriptor must be followed
by a datatype which defines the type of the column. Column names and
types must be individual tokens, i.e. separated by whitespace.
CRDE supports 6 datatypes: c, i, l, d, f, and $. Each type directly
relates to a native C datatype.
c A character string between 1 and 256 characters. The c datatype
must be immediately followed by a number between 1 and 256.
This is the maximum length of the string. The c type is
equivalent to the C type char[]. Thus the type c26 is equivalent
to char[26], and so on.
i An integer value corresponding to the C type int. In Turbo C,
an int has a range from -32767 to 32767.
l A long integer value corresponding to the C type long int. In
Turbo C, a long int has a range from -2147483647 to 2147483547.
d A date. The d type corresponds to the date_t type defined in
date.h. The Turbo C version of date_t is equivalent to type
long int. CRDE supplies several functions for creating and
manipulating dates. A date type can hold any date between
1/1/1 and 12/31/32767.
f A floating point value. The f type directly corresponds to the
C type double. In Turbo C, a double has a range of 1.7E-308 to
1.7E+308.
$ A dollar value. The $ type corresponds to the dollar_t type
defined in crde.h, which in the Turbo C version is type double.
- 9 -
Since all CRDE datatypes directly correspond to C datatypes, it makes
sense that a row in CRDE then, would correspond to a C structure. This
is an important feature of CRDE, making transferring data between CRDE
tables and C variables relatively easy. A row in the sups table would
correspond to the C declaration
struct {
int s;
char sname[26];
char city[26];
int status;
}
This becomes important when you want to transfer data between C
variables and CRDE tables. Note: since C variable names cannot contain
characters like '#', they were omitted from the declaration. In fact,
none of the column names have to appear in the structure, only their
types and sizes are important.
Defining a primary key
CRDE supports the relational idea of defining a primary key on a table.
The primary key is made of one or more column(s) of the table. A table
which has a primary key is generally referred to as being a keyed table.
Similarly, a table without a primary key is referred as being non-keyed.
The key values of a row in the table consist of all the values in every
column in the row which make up the primary key. By definition, every
row in a keyed table can be uniquely identified by its key value(s).
Consequently, no two rows in a table can have the same key value(s).
A primary key prevents you from adding rows to a table which have the
same key values. In CRDE, up to 8 columns may be part of the primary
key. CRDE prevents two or more rows in a table from having identical
values in each of the assigned columns. CRDE also supports non-keyed
tables (tables which do not have a primary key). It is good relational
practice, however, to define a primary key on a table. Doing so will
help you to normalize your tables and have a minimum amount of redundant
information, and can also improve search times. Tables are usually
linked to one another by all or part of their primary keys.
You can define a primary key in a table descriptor by prepending any
columns you want as part of the key with an asterisk '*'. The '*' must
be directly attached to the column's name, and not be separated by
whitespace. Columns which are part of the primary key can be in any
order, and do not have to be consecutive. However, the order can have
other side effects. In general, you should place all key columns at the
beginning of a table, with the most significant column first, the next
most significant column, second, etc. In the sups table above, the "s#"
column is the key field of the table. It uniquely identifies a row, thus
every row should have a unique value for "s#". To make "s#" the primary
key of the table you would define the table as
t = tcreat("sups", "*s# i, sname c26, city c26, status i");
This would prevent you from entering to suppliers who have the same "s#".
- 10 -
As previously stated, a primary key can be made of up to 8 columns.
This orders table contains a supplier # "s#", a part# "p#" and a
quantity "qty". The primary key is on both "s#" and "p#".
t = tcreat("orders", "*s# i, *p# i, qty i");
This table would allow you to have orders with the same supplier # or
part # but not both.
Defining a primary key in CRDE has another advantage as well. To
enforce the primary key, CRDE creates an index, called the primary
index, on the table. Whenever you insert a row into a table, CRDE
uses this index to make sure that the primary key will not be
violated. However, CRDE can also use this index to help speed up
searches and other relational operations. Since the bulk of relational
operations will involve the primary key of a table, this will
significantly improve the performance of many operations. You never
have to remind CRDE to update the primary index. CRDE updates it
automatically whenever you insert, change, or delete rows in a table,
using any CRDE function, so it is always kept up to date.
When designing a table, your first thought should be of what will be
the table's primary key. The key should be a value which would not be
duplicated under normal circumstances, such as an id# or some other
unique identification value. A poor choice for a primary key, usually
made by beginners, is a name or some other large character field.
But who is to say that two suppliers might not have the same name?
Furthermore, you might accidently misspell the name, or use the wrong
case, leave trailing blanks on the end of it, making it difficult to
correctly identify duplicate keys.
The best choices for key columns are columns which have the types i,
l, and d. Short c columns can also make good keys if used correctly
(for example, an id which contains letters in it, such as a serial#).
c columns longer 15 or 20 characters are probably poor choices for keys,
although CRDE support keys up to 256 characters in length. Not all
columns in the primary key do not have to be of the same type either.
You may frequently have primary keys which are made of i, l, d, and c
columns in combination.
f and $ columns are the worst choices for primary keys, although CRDE
does support them as such. This is because of the nature of floating
point values on computers. A computer can only measure a floating point
value to a certain number of significant digits, whereas it can
calculate i, l, c, and d columns exactly. Thus, the results of floating
point expressions are not always accurate. Rather they are
approximations of what the result should be. Consider the problem of
a simple floating point division:
double f = 1.0 / 3.0;
The answer to this problem is f = 0.333333333 etc... But since the
computer can only store 19-20 digits, it must truncate the value. The
result is not really 1.0 / 3.0, but an approximation. Two approximations
of the same value might not always be identical. The bottom line is
that you should avoid using f or $ columns in primary keys if at all
possible.
- 11 -
Creating a database
Creating a good relational database can be challenging, especially if
you are not familiar with the relational model. Beginners will often
try to lump all data together into a single table rather than distribute
data among several tables and relate them together. This can lead to
databases which are very redundant and inflexable.
The kitchen sink approach
Suppose you wanted to maintain a list of orders made for parts from
various suppliers. Your first draft of the database looks like this
orders = tcreat("orders", "sname c26, city c26, status i,"
"pname c26, color c11, qty i");
Each order contains the supplier's name, city, and status, the part's
name and color, and the quantity ordered. This seems harmless enough.
Now lets make some orders:
sname city status pname color qty
--------------- --------------- ------- --------------- ------- -------
Smith, John Boston 5 bolt red 100
Smith, John Boston 5 screw red 200
Smith, John Boston 5 screw blue 300
Smith, John Boston 5 nut gray 400
Smith, John Boston 5 bolt gray 500
Smith, John Boston 5 nail red 600
Jones, Ben New York 10 bolt red 100
Jones, Ben New York 10 screw red 100
Jones, Ben New York 10 nail red 100
Barnes, Frank Boston 15 bolt red 200
Barnes, Frank Boston 15 screw red 50
Landon, Mark Auburn 20 screw blue 100
Landon, Mark Auburn 20 bolt gray 500
As you add more and more information to the table, the problems with
this arrangement become apparent. There is a large amount of redundant
information in the table. The same names and cities appear several
times in the table. Every time an order is entered, redundant
information is added over and over again.
This type of organization is also very inflexible. For example, if
John Smith moved to Chicago, you would have to go through and change
every row which contained John Smith. Suppose another supplier named
John Smith lived in Boston. Although unlikely, it is not impossible.
There would be no way to differentiate between the two.
- 12 -
The relational approach
The relational solution to this problem is to divide the one large
table into smaller tables by organizing data into groups: suppliers,
parts, and orders. Suppliers and parts are given special id #'s so
that they may be easily identified. The orders table references the
supplier and part tables though its "s#" and "p#" columns.
sups = tcreat("sups", "*s# i, sname c26, city c26, status i");
parts = tcreat("parts", "*p# i, pname c16, color c11");
orders = tcreat("orders", "*s# i, *p# i, qty i");
sups
s# sname city status
------- --------------- --------------- -------
1 Smith, John Boston 5
2 Jones, Ben New York 10
3 Barnes, Frank Boston 15
4 Landon, Mark Auburn 20
5 Andrews, Jim New York 5
parts
p# pname color
------- --------------- -----------
1 bolt red
2 screw red
3 screw blue
4 nut gray
5 bolt gray
6 nail red
orders
s# p# qty
------- ------- -------
1 1 100
1 2 200
1 3 300
1 4 400
1 5 500
1 6 600
2 1 100
2 2 100
2 6 100
3 1 200
3 2 50
4 3 100
4 5 500
Breaking up your tables in this manner is called normalization. A table
is said to be normalized if it contains a minimum amount of redundant
information.
- 13 -
Chapter 4
Filling Tables with
Information
Loading tables with tload
Now that we have a sample database set up, we need to fill it with
information. CRDE provides 3 functions for inputing data into tables:
tload, tinsert, and treplace. The simplest of the three is tload.
tload is declared in crde.h as
int tload(TABLE *t, ...);
tload expects a variable series of arguments, one for every column in t.
Together the arguments form a row. tload then inserts the row into the
table. For example, to load a supplier s# = 1, sname = "Smith, John",
city = "Boston", status = 5, into the sups table would look like
tload(sups, 1, "Smith, John", "Boston", 5);
The table sups now contains one row. Data must be entered for every
column in the exact same order that they appear in the table's table
descriptor, and you may not skip column(s). The argument for each
column must be the exact type for that column (c columns expect a
char[], or char *, as an argument). If an argument is not of the
correct type, the data you are loading may not be what you expect.
This problem can commonly occur in l columns. For example, suppose
the sups table were defined as
sups = tcreat("sups", "*s# l, sname c26, city c26, status i");
the above tload statement would be invalid. Why? because the first
argument is an int value whereas the "s#" column is of type l or long
int. By default, C converts constant 1 into an int. To load 1 as the
suppler number you would have to explicitly cast it as type long by
using a typecast (long int) or the L suffix. A similar problem would
occur is "s#" were of type f (which in this case it shouldn't be
since it is part of the primary key). A way around having to use a
specific typecast with f columns is to let the compiler know that you
are entering a floating-point value by writing the number as 1.0.
C interprets floating point constants to be of type double by default.
In a similar vein, you should be sure to make sure that the constant
value is not out of range. For example, loading 1000000 into an int
column (in Turbo C anyway). tload does handle the problem of char *
- 14 -
arguments which are too long however. If the argument for a c column
to too large to fit in the column, tload will automatically truncate
it to fit into the column's specified length. tload does no type
checking, nor does it verify that the correct number of arguments have
been entered. Entering the wrong number of arguments or the wrong type
for an argument will leave part or all of the columns in the row
undefined.
You can load any datatype into tables, even date types. For example,
emps = tcreat("emps", "e# i, ename c26, hired d, salary f");
tload(emps, 100, "Barnes, Don", mkdate(4, 2, 1978), 35000.0);
tload(emps, 101, "Carnegie, Jamie", mkdate(5, 7, 1988), 25000.0);
tload(emps, 102, "Donald, Markus", mkdate(11, 13, 1985), 42500.0);
mkdate is declared in date.h. It receives three parameters, month,
day, and year, and returns the date_t equivalent. Also note how the
salaries and appended with a .0 to mark them as double values. You
can also use variables in tload. Using variables can be safer than
constants because C converts a constant to the correct type when it
assigns a variable.
The following function demonstrates this point by loading a supplier
into the sups table based upon the parameters received. Any constants
passed to LoadSup are automatically converted to the correct type by C
for you. Using this method also eliminates any problems with entering
the correct number of arguments. C (new-style anyway) will prevent you
from entering the wrong number and/or types into the row by issuing a
compilation error.
TABLE *sups;
int LoadSup(long s, char *sname, char *city, int status)
{
return tload(sups, s, sname, city, status);
}
sups = tcreat("sups", "*s# l, sname c26, city c26, status i");
LoadSup(1, "Smith, John", "Boston", 5);
tload returns 0 if no errors occurred.
Error -41
The most common error which can occur while loading or inserting rows
is error -41. Error -41 occurs whenever you try to insert a row into
a table that already contains the key found in the row you are trying
to insert. For example
i = tload(sups, 1, "Smith, John", "Boston", 5);
i = tload(sups, 1, "Hurt, Frank", "Denver", 10);
- 15 -
The first tload statement will return 0 meaning that the row was
successfully loaded (unless a supplier with an "s#" 1 already exists).
The second tload statement however will return -41, meaning that a row
with the same key (1) already exists and that the row was not loaded.
Unlike other error codes, -41 is not really considered to be a error
but rather an indicator that the primary key would be violated by
inserting the row. -41 is also the only error code which will not
cause a function to abort. Normally when an error occurs, a CRDE
function will immediately halt, do any cleaning up necessary and return.
Not so with error code -41. This is why sometimes a function will
return successfully, but also set terrno to -41. All that means is
that during the course of operation, the function tried to insert one
or more row(s) into a table which already contained a row(s) with the
same key.
Inserting rows
CRDE provides another function for inserting values into a table
called tinsert. tinsert is declared in crde.h as
long tinsert(TABLE *t, void *rows, long n);
tinsert expects rows to point to a buffer containing the row you want
to insert. The buffer should be a structure defined exactly like the
table's table descriptor. For example, to insert John Smith into the
sups table with tinsert would look like
typedef struct {
int no;
char sname[26];
char city[26];
int status;
} sup;
sup s;
/* fill structure with values */
s.no = 1;
strcpy(s.name, "Smith, John");
strcpy(s.city, "Boston");
s.status = 5;
/* insert structure into table */
tinsert(sups, &s, 1);
tinsert returns the number of rows inserted, in this case 1. You can
insert entire arrays into tables using tinsert. rows should point to
the first element in the array. n is the number of elements to insert.
To insert an array of 10 suppliers into the sups table would look like
sup s[10];
/* fill s with info */
tinsert(sups, &s, 10);
- 16 -
Any rows in the array for which a key already exists in the table are
not inserted. tinsert will return the number of rows that were
actually inserted into the table.
Replacing rows
Normally when you try to insert a row into a table where a duplicate
key already exists, CRDE will not let you insert the row. However,
sometimes you may want to throw out the existing row and replace it
with the new row. CRDE gives you this option to override this by
using the treplace function. treplace is declared in crde.h as
long treplace(TABLE *t, void *rows, long n);
treplace is identical to tinsert. The only difference being that
tinsert inserts rows and treplace replaces them, which means if a row
with the same key as the row you are trying to insert already exists,
the old one is thrown out and the new one inserted. If a duplicate
key does not exist, then treplace simply inserts the row into the table.
Note: By default, tload always inserts rows. There is no complementary
function by which you can load values which replace rows. If you want
to replace a row you must use treplace.
Write modes
Normally when you make call a CRDE function which alters a table, such
as tload, tinsert, or treplace, CRDE automatically writes those changes
to disk after every function call. This is to protect data integrity.
If something were to go wrong (such as a power-outage) all your
information would be safely on disk. In CRDE this is called normal
mode. By default a table is in normal mode when it is opened or created.
However, there are some times when you may want to prevent CRDE from
instantly writing your changes to disk after every function call. The
primary reason is speed. Writing every change to disk as soon as it
occurs can take an enormous amount of time, especially if you are
making a lot of relatively small changes in succession. That's why
CRDE has another mode, called write-cache, or writec, mode. If a
table is in this mode, CRDE trys to keep as many changes buffered in
memory as possible, instead of writing them immediately to disk.
CRDE provides two functions for setting a table's mode to either normal
or write-cache. These are tnormal and twritec respectively. twritec
puts a table in write-cache mode. tnormal returns a table to normal
mode. You usually use twritec to place a table in write-cache mode
when you are making "batch" changes, or several changes in succession.
For example, suppose you wanted to load the sups table with a bunch
of new suppliers,
- 17 -
twritec(sups);
tload(sups, 1, "Smith, John", "Boston", 5);
tload(sups, 2, "Jones, Ben", "New York", 10);
tload(sups, 3, "Barnes, Frank", "Boston", 15);
tload(sups, 4, "Landon, Mark", "Auburn", 5);
tload(sups, 5, "Andrews, Jim", "Rochester", 5);
/* load rest of suppliers here */
tnormal(sups);
tnormal returns the table to normal mode. It also writes all changes
made while the table was in writec mode to disk. You can explicitly
flush a table's unwritten buffers to disk at any without leaving writec
mode by using the tflush function. Also, CRDE can flush a table's
buffers automatically if it needs more memory or if the buffers
become full. You can use the tmode function to determine what mode a
table is currently in. tmode will return one of four constants defined
in crde.h depending upon what mode the table is currently in.
T_NORMAL table is in normal mode.
T_WRITEC table is in write-cache mode.
T_TEMP table is a TEMP table.
T_STATIC table is a STATIC table.
T_TEMP and T_STATIC are only returned by specially designated temporary
tables which operate in their own special mode which is similar to
writec and cannot be changed. Temporary tables are explained in detail
in Chapter 11.
- 18 -
Chapter 5
Basic Table I/O
Opening tables
This chapter describes some basic table operations such as opening
and closing tables, renaming tables, erasing tables, and indexing tables.
Once a table has been defined and exists on disk, you only need to open
it to use it again. You open a table with the topen function. For
example:
TABLE *t;
t = topen("sups");
topen is declared in crde.h as
TABLE *topen(char *name);
topen expects the table's name as a parameter, which must be a legal DOS
pathname less than 64 characters (no wildcards or extension). Once the
table is open, you can perform any operations on the table you wish.
Integrity checking
topen does a number of integrity checks to validate that the table is
not corrupt. topen can detect almost any type of corruption in the
table, and will attempt to fix the problem if necessary. The most
common type of corruption is an index which has become out of sync
with the table. If topen detects this, it will automatically rebuild
the index. However, there are some things which even topen can't handle.
If topen cannot fix a damaged table, it will not open it, but return
an error code instead. Your only alternative is to attempt to repair
the table with the trepair function. Fixing damaged tables is
described in more detail in Chapter 15, Repairing Tables.
Closing a table
You should always close a table when you are finished with it. This will
insure that all unwritten data is written to disk and that any memory
that the table is holding is released. You close a table with the
tclose function. tclose is declared in crde.h as
int tclose(TABLE *t);
- 19 -
For example, to close the above table, you would issue a
tclose(t);
Checking a table's accessability
You can check if a table exists on disk with the taccess function.
taccess is declared in crde.h as
int taccess(char *name, int mode);
name must be a valid table name. You can check is a table exists by
calling taccess with mode set to 0. taccess returns 0 if the table
exists or -1 if it does not.
Renaming tables
You can rename a table with the trename function. trename renames the
table specified, including all indexes. It is recommended that you use the
trename function whenever you want to rename a table. trename is declared
in crde.h as
int trename(char *old, char *new);
trename renames table old to new. old and new must both be valid table
names. trename will not overwrite a table by the name new if one already
exists. If one does, trename will not rename the table.
Erasing tables
You can erase a table with the terase function. terase erases the
specified table and all associated indexes. It is recommended that you
always use terase to erase a table. terase is declared in crde.h as
int terase(char *name);
name must be a valid table name.
Secondary Indexes
Creating Indexes
An index, often called a secondary index in CRDE, is a special type of
file which can help CRDE perform very fast searches. An index in CRDE,
is very much like an index in a book. While the subjects in a book is
not in any particular order, the index is always maintained in
alphabetic order. Whenever you want to find a particular subject in
the book, you look up its reference in the index. This is very
similar to how indexes are used by CRDE. You can create a secondary
index on a table with the tindex function. tindex is declared in
crde.h as
int tindex(TABLE *t, char *id);
- 20 -
tindex returns 0 if the index was successfully created.
Index descriptors
id is a asciiz string which contains an index descriptor. The index
descriptor is a list of all the columns which are part of the secondary
index. Each column in the list must be separated by whitespace,
i.e. all characters classified by the standard library function
isspace() and the comma ','. To create an index on the "sname" column
of the sups table would look like
tindex(sups, "sname");
In addition, any columns in the index which a prepended with an
exclaimation point '!' are indexed in descending order. Which means a
valid index on the "sname" column would also be
tindex(sups, "!sname");
In general however, you should avoid using descending keys in indexes.
Since it doesn't matter to CRDE whether an index key is in ascending
or descending order, just like it doesn't matter whether a telephone
book is ordered from A-Z or Z-A. There is no advantage to using one
over the other, except in some rare occasions when CRDE requires that
the index be read sequentially. This only really occurs in the tsort
function. Keeping you indexes in ascending order will help avoid
confusion.
You can also create compound indexes by including more than one column
in the index descriptor. Secondary indexes may contain up to 8 columns.
For example, to create a compound index on the "city" and "sname"
columns
tindex(sups, "city, sname")
You can think of compound indexes as being ordered like words in a
dictionary. Words in a dictionary are ordered by their first letter,
and then their second, and then their third, etc. Similarly, suppliers
in the above index are ordered by city and those in the same city are
ordered by their supplier name. Thus this index would be useful in
searches for suppliers in a particular city, or those in a particular
city with a particular name. It would not, however, be useful for
finding just a particular name, just as it would be fruitless to
search for a word in a dictionary without knowing its first letter.
Once you create the index, you never have to remind CRDE to update or
use it. CRDE does it for you automatically. The index is now part of
the table and is opened and closed along with the table. The index
remains part of the table until such time as it is dropped specifically
by you. The advantage of secondary indexes speed. On large tables,
indexes are especially attractive because search times for exact values
can be reduced to less than a second in most cases.
- 21 -
However you should be sparing when defining secondary indexes, only
using them when the increase in performance is significant enough to
warrant it. While secondary indexes can increase search times, they
will slow other operations such as inserting, changing, and deleting
rows. This is because CRDE must update the indexes whenever changes
are made to the table. Secondary indexes also take up additional disk
space. And sometimes creating a secondary index on a column(s) may
not always produce an increase in performance. CRDE is exceptionally
fast at sequential searches. Sometimes, on smaller tables, a
sequential search can be as fast as an indexed one. CRDE may also
build a temporary index on its own when performing complex query
operations. The best time to use a secondary index is on a large
table where you need to access exact values in a column. For example,
you might want to place a secondary index on the "city" column if you
need to find a supplier who lives in Boston. Poor choices for
secondary indexes are columns which contain lots of duplicate values,
f and $ columns, large c columns, columns which hold weights,
quantities, etc.
Primary index vs. Secondary Indexes
When you create a primary key, an index (the primary index) is created
which is similar to an index created by specifying those columns marked
with an asterisk '*' read from left to right in ascending order. For
example, the primary index create on the orders table defined as
orders = tcreat("orders", "*s# i, *p# i, qty i");
would create a primary index similar to a secondary index created by
the statement
tindex(orders, "s#,p#");
For purposes of searching, sorting, and joining, etc. the two types of
indexes are functionally equivalent. Thus it would be redundant to
create the above secondary index on the orders table.
Despite the similarities, there are some basic differences between the
types of indexes:
A primary index is created when the table is defined and cannot
be removed afterwards. Secondary indexes are created with the
tindex function, and can be removed by tdropindex.
A primary index cannot contain duplicate values, while a
secondary index can.
- 22 -
Rebuilding indexes
You can also rebuild indexes on a table with the tindex function.
Simply specify the index to rebuild in the index descriptor. Rebuilding
indexes is useful if you think that the index may be corrupt. topen
will also rebuild an index automatically if it detects that the index
is corrupt.
tindex(sups, "sname"); /* create index */
/* ... */
tindex(sups, "sname"); /* rebuilds index */
Dropping indexes
You can drop indexes from a table by using the tdropindex function.
tdropindex is declared in crde.h as
int tdropindex(TABLE *t, char *id);
tdropindex will drop the index exactly matching the index descriptor
specified. To drop the "sname" index from a table would look like
tdropindex(sups, "sname");
Redundant indexes
It is possible when creating several secondary indexes, that two or
more indexes may be redundant. An obvious example is shown above:
creating a secondary index identical to the primary index. There
are other possibilities for redundancy. CRDE prevents you from
creating duplicate secondary indexes because whenever you do so,
tindex simply rebuilds the original. However, it does not prevent
you from creating redundant indexes. You must do this on your own.
Redundant indexes are those which can be derived from, or used in
place by, existing indexes. One example of a redundant index is
creating a secondary index identical to the primary index. Here are
some other examples of redundant indexes:
/* the second index is simply an inverse of the first */
tindex("sname");
tindex("!sname");
/* the second index is simply an inverse of the first */
tindex("s#, !city");
tindex("!s#, city");
/* the second two indexes are just more generals versions of the
first */
tindex("city,sname,status");
tindex("city,sname");
tindex("city");
- 23 -
Chapter 6
Viewing a Table
The tview function
CRDE provides an extremely powerful view facility which you can integrate
directly into your programs. With a single function call you can
- view any CRDE table in any text window on your monitor
with your choice of color for text, frame, and background. The
table is automatically adjusted to the window and you can
scroll both horizontally and vertically through a table.
- view only those columns you want to see, in any order you
wish.
- view the rows in any sorted order, or in the order they were
originally inserted into the table.
- create a pick list to select rows from a table.
CRDE's view function is called tview. tview is declared in crde.h as
int tview(TABLE *t, char *cl, char *id, int x1, int y1, int x2,
int y1, int cattr, int fattr, int battr, void *rec);
t is the table to be viewed. t must point to an open table.
cl is an asciiz string listing all the columns you want to view. Each
column name must be separated by whitespace. You may order the columns
in any order you wish. If cl is NULL then every column will be
displayed in the order they appear in the table's table descriptor.
id is an index descriptor describing the order in which the rows in
the table will appear. If id is NULL the rows will appear in the
order they were originally inserted into the table.
x1,y1,x2,y2 are the coordinates of the window the view will appear in.
The coordinates can range from 1,1 to 80,25. But the window must have
a minimum width of 5 and minimum height of 1. Some columns, such as
large c columns, may be too wide to fit in the specified window.
If so, then the column will be truncated so that it fits.
cattr is the text attr for all values in the columns.
fattr is the text attr for the view frame.
- 24 -
battr is the text attr for any portion of the window not filled by the
view.
rec should point to a buffer large enough to hold one row in the table.
When you exit the tview function, the row that the cursor was last on
will be placed into rec. You can use this feature to use tview as a
pick list to select a row from a table. You can keep tview from
copying a row into the buffer by assigning rec to NULL.
tview offers a full range of movement up, down, left, right, beginning,
end. If the table is too large to fit in the specified window, tview
can scroll the table both horizontally and vertically so that you can
see every portion of the table.
Cursor key Action
Up Move up a row.
Down Move down a row.
Left Move left one column.
Right Move right one column.
PgUp Move up a page.
PgDn Move down a page.
Home Move to the first row.
End Move to the last row.
Ctrl-Left Move to the first column.
Ctrl-Right Move to the last column.
You can exit from tview by pressing either the Enter key or the Esc key.
Which key you hit determines what value is returned by tview. This is
particularly valuable when using tview as a pick list. You might use
tview to select a specific row then process the row if the user
presses Enter, or cancel the operation if Esc is pressed.
Enter Exit tview returning 13.
Esc Exit tview returning 27.
Selecting which columns you want to view can be an invaluable feature
when you want to restrict the information a particular user can see.
For example, you might want to let a user view an employees table
without letting them see the employee's salary. tview always displays
the current row number in the leftmost column, regardless of which
columns you decide to display.
Selecting the sort order you want the table to be displayed in can be
invaluable as well. However, when viewing very large tables (+5000
rows) in sorted order, tview can slow down dramtically. Its usually
best to view large tables in the order in which they were inserted.
Limitations of tview
tview can only be used for viewing tables. You cannot insert, update,
or delete rows from a table with tview. tview adds about 10k to your
programs.
- 25 -
Chapter 7
The Search-Related Functions
The seven search-related functions
The CRDE library contains seven special functions classified as the
search-related functions. These functions allow you to perform actions
on subsets of a table simply by specifying the rows that you want
to perform the operation on. The seven search-related functions are
tselect selects rows from a table
tproject projects a column from the table
tdelete deletes rows from a table
tchange changes rows in a table
tscan scans rows in a table
tlookup looks up a row in a table
tget gets rows from a table into a C array
The search expression
Every search related function expects a NULL terminated list of
arguments which formulate a search expression. The search-related
function will only perform its operation on those rows which match
the criteria in the search expression. Lets start with an example:
tselect. tselect is declared in crde.h as
TABLE *tselect(char *name, TABLE *t, ...);
Every search related function's parameter list ends with an
ellipsis(...). A call to tselect will have the format
answer = tselect(name, t, colname1, relop1, value1, colname2,
relop2, value2, ..., NULL);
A search expression is made up of zero or more conditions followed by
a terminating NULL. A condition is a colname[n], relop[n], value[n]
combination.
colname[n] must be a column name from table t.
relop[n] is one of six relational operators defined in crde.h. Valid
relops are:
- 26 -
EQ equals, ==
GT greater than, >
LT less than, <
LE, LTE less than or equal to, <=
GT, GTE greater than or equal to, >=
NE, NEQ not equal to, !=
value[n] must be an argument which evaluates to a constant. The type
of the argument must be identical to the type of colname[n] (c columns
expect a char * as a value). For example, suppose you wanted a list
of all suppliers who live in Boston. You could accomplish this with
the following call:
answer = tselect("answer", sups, "city", EQ, "Boston", NULL);
tselect returns a table as a result. The structure and primary key
of the created table are identical to the table being queried, in
this case sups. tselect creates a table called answer identical in
structure to sups containing all the rows from sups where the "city"
column equals "Boston". Notice the terminating NULL. Every search
expression must end with a NULL.
A search expression may contain up to 16 conditions. A search
expression may also contain no conditions. In this case, the search
expression would only consist of the terminating NULL. A search
expression with no conditions will select every row from the table.
The search engine
CRDE handles searches with its search engine. The search engine
parses the search expression and determines the fastest way to perform
the search, regardless of how the search expression is specified.
The search engine knows about your indexes and will use them
automatically to assist in the search.
Search-related operations
Selecting rows
tselect is one of several functions which returns a table as a result.
tselect returns a table identical in structure as t, containing all
the rows from t matching the search expression.
answer = tselect("answer", sups, "city", EQ, "Boston", NULL);
answer contains all the rows from sups where the supplier lives in
Boston. The structure of the answer table is identical to sups.
It has the same columns and column types. It also has the same
primary key. It will not have any secondary keys, even if sups
does. Although CRDE considers secondary keys to be part of a table,
they are not considered part of the table's structure.
- 27 -
Projecting rows
Projecting columns from a table is similar to selecting them, except
you have the option to choose only those columns you want to appear
in the answer table. You can project columns from a table with the
tproject function. tproject is declared in crde.h as
TABLE *tproject(char *name, TABLE *t, char *pl, ...);
The pl parameter is an asciiz string containing a list of those columns
you want to appear in the resulting table. You can select only those
columns you want, in any order that you want. For example, to select
only a supplier's name and city from the sups table would look like
answer = tproject("answer", sups, "name,city", NULL);
Notice there are no conditions in the search expression, just the
terminating NULL. A search expression with no conditions selects
every row in the table. If you wanted the city column to appear
first, just switch them around
answer = tproject("answer", sups, "city,name", NULL);
Of course, you could also project the name and city columns from only
those suppliers in Boston with
answer = tproject("answer", sups, "name,city", "city", EQ, "Boston",
NULL);
The structure of the new table will be identical to the columns listed
in pl in the same order they appear in pl. The type of each column is
identical to its type in t.
Defining a primary key with tproject
The above query create table a table with two columns which has a
structure identical to "name c26, city c26". The table has no
primary key. You can specify a primary key on the projected table
by prepending any columns with an asterisk '*'. Like in a table
descriptor, a maximum of 8 columns may be part of the primary key.
The projected table by default does not inherit a primary key unless
it is specifically specified. For example, if you projected the "s#"
column from sups, the resulting table would not inherit the primary
key of "s#" unless you specifically used "*s#". Defining a primary
key on the projected table can very useful, especially in removing
duplicate values from the result table. For example suppose you
wanted a list of all cities where there are suppliers. Defining a
query like
answer = tproject("answer", sups, "city", NULL);
- 28 -
might not have the exact effect you would like. Why? because if more
than one supplier lived in the same city, that city would appear more
than once in the list. If more than one supplier lives in the same
city, you only want that city to appear once.
You can get the result you want by defining a primary key on the
"city" column.
answer = tproject("answer", sups, "*city", NULL);
The resulting table will have a primary key on the "city" column.
Because, by definition, no two rows in a table may have the same
key values, CRDE will automatically filter out any duplicates.
Similarly, tproject can be used to change or remove the primary key
on a table. For example, if we wanted to remove the primary key from
the sups table you could do this with
answer = tproject("answer", sups, "s#,sname,city,status", NULL);
answer would be a copy of sups without a primary key.
Deleting rows
You can delete rows from a table with the tdelete function. tdelete
is declared in crde.h as
long tdelete(t, ...);
tdelete is fairly straightforward. All rows matching the search
expression are deleted from the table. For example, to delete
supplier #5 from the sups table
tdelete(sups, "s#", EQ, 5, NULL);
tdelete returns the number of rows deleted.
How rows are deleted
tdelete doesn't physically delete rows from a table. Rather it marks
the space as unused. The space is reused when you re-insert rows into
the table. Because CRDE reuses the space in tables caused whenever a
deletion occurs, you do not have to "pack" your tables like you do in
other data managers. A special case is when you want to remove all
rows from a table. This is usually referred to as emptying a table.
You can empty a table with tdelete by issuing a
tdelete(t, NULL);
However, this would leave the entire table unused and waste a lot of
disk space. CRDE provides a function to remove all the rows from a
table, which does physically reclaim any unused space, called tempty.
tempty is declared in crde.h as
tempty(TABLE *t);
- 29 -
You should always use tempty when you want to remove every row from a
table for this reason. Using tempty is also much quicker than the
tdelete method.
Changing rows
You can change values in a table by using the tchange function. The
tchange function is declared in crde.h as
long tchange(TABLE *t, ...);
tchange returns the number of rows changed. tchange is little
different from the other search-related functions. It expects not one,
but two NULL terminated lists of arguments. The first is a list of
changes, followed by the standard search expression. A sample call
to tchange will have the format
tchange(t, chcol1,chval1, chcol2, chval2, ..., NULL, colname1,
relop1, value1, colname2, relop2, value2, ..., NULL);
A change list is made up to 16 changes. A change consists of a
colname[n], chcol[n] combination.
chcol[n] is the column name of the column to be changed.
chval[n] is the value that the column will be set to in every row which
matches the search expression. chval[n] must resolve can be a
constant, variable or function result which has a type identical to
chcol[n]. c columns expect a type char *.
For example, suppose you want to change part #3's color to purple.
You could do this with a simple tchange call
tchange(parts, "color", "purple", NULL, "p#", EQ, 3, NULL);
tchange can be used to make global changes. For example, at the end
of a year you might want to reset every supplier's status to 0. To do
this with tchange would look like
tchange(sups, "status", 0, NULL, NULL);
A common error made with tchange is that you must remember to specify
two NULLs instead of just one.
Scanning rows
You can count the number of rows which match a search expression by
using the tscan function. tscan is declared in crde.h as
long tscan(TABLE *t, ...);
tscan returns the number of rows scanned.
- 30 -
tscan really doesn't do anything accept count the number of rows which
match the search expression. tscan is not usually used much in
applications.
Looking up a row
You can determine if a row matching a given search expression exists
with the tlookup function. The tlookup function is declared in crde.h
as
int tlookup(TABLE *t, ...);
tlookup returns 1 if a row matching the search expression was found or
0 if it was not. tlookup stops searching after the first match is found.
Getting rows
tget is similar to tselect, except that the rows that are selected are
placed in a C array rather than a table. tget is declared in crde.h as
long tget(TABLE *t, void *array, long n, ...);
tget places up to n rows that match the search expression into the
buffer pointed to by array. The buffer should be an array of at least
n structures identical the structure of the table t. It is important
to remember that the rows that are extracted with tget are only copies
of the rows in the table. The rows retrieved by tget are not removed
from the table, nor will changing the rows in the array have any affect
on the original values in the table.
The following exmaple places every supplier with a status > 5 into an
array buf.
typedef struct {
int s;
char sname[26];
char city[26];
int status;
} sup;
sup buf[20];
int i;
i = tget(sups, buf, 20, "status", GT, 5, NULL);
tget returns the number of rows actually retreived, which in this case
will be between 0 and 20.
tget is often used in conjunction with treplace. You can get rows
from a table with tget, perform operations on them, and return them to
the table with treplace.
- 31 -
"..If" functions
Although a search expression is capable of handling many searches, it
cannot handle all of them. Fortunately, CRDE provides a version of each
of the above functions which allow unlimited searching capabilities.
These are referred to as "..if" functions. They are, respectively
tselectif
tprojectif
tdeleteif
tchangeif
tscanif
tlookupif
tgetif
Each is identical in use and declaration to its brother function in
every way except one. Each excepts an additional parameter which can
be used as a filter to create user-defined search conditions. In each
declaration, the user-defined function action, appears directly before
the ellipsis in a search related function. For example, tselectif
is declared as
TABLE *tselectif(char *name, TABLE *t, int (*action)(void *), ...);
action is a user-defined function which must accept a void * as a
parameter and return an int. Every row which matches the search
expression is passed to action (which is referenced by void *) for
further evaluation. The result of action determines whether or not
an operation will occur.
Result of action What happens
> 0 Row is selected.
= 0 Row is not selected.
< 0 The function automatically aborts as
if an error occurred. If the "..if"
function returns a TABLE * then it will
return NULL otherwise the result of
action is returned by the function.
terrno is set to the result of action.
By using "..if" functions, programs can create virtually any search
criteria. For example, suppose you wanted to select all suppliers
who either live in Boston or Denver. You cannot create this sort of
search expression because only those rows which match every condition
in the expression are selected. Specifying
answer = tselect("answer", sups, "city", EQ, "Boston", "city",
EQ, "Denver");
would not answer the question, because CRDE would try to select every
supplier would lives in Boston and Denver, an impossibility. This
problem however, is easily handled with a simple "if" function.
- 32 -
Example #1.
typedef struct {
int no;
char sname[26];
char city[26];
int status;
} sup;
int iffunc(void *rec) {
return strcmp(((sup *)rec)->city, "Boston") == 0 ||
strcmp(((sup*)rec)->city, "Denver") == 0;
}
answer = tselectif("answer", sups, iffunc, NULL);
No search expression is supplied, so tselectif passes every row in the
table to iffunc for evaluation. iffunc returns true if the city field
equals Boston or Denver. Notice that since rec must be a void *, you
must typecast it to extract data from the row.
Example #2.
Select all parts which are red ignoring any case differences.
int ColorIsRed(void *rec) {
return stricmp(((part *)rec)->color, "RED");
}
answer = tselectif("answer", parts, ColorIsRed, NULL);
Example #3.
Change all the part's colors to uppercase.
The user-defined function for tchangeif works a little differently
than in other "..if" functions. Any changes made to the row passed
to action are reflected in the table (if, of course, action returns > 0).
Thus tchangeif allows you to perform user-defined changes as well as
user-defined selection. For example, to change every part's color to
uppercase would look something like
int ColorUpr(void *rec) {
strupr(((part *)rec)->color));
return 1; /* change every row */
}
tchangeif(parts, CityUpr, NULL, NULL);
- 33 -
The NULLs are required even when no change list or search expression
is specified. Note: any changes made in the change list are made after
the row is passed to action. action may modify the record passed to
it without harm (although this does have side effects when used in
tchangeif). action can perform operations on other tables based on
the information passed to it. action can even perform queries on the
host table (the table whose rows are being passed to action). This
allows for extremely powerful and complex querying capabilities.
However, there is one restriction. action is disallowed from altering
t in any way. When any "..if function" calls action, t is automatically
placed in a special read-only mode. Because of the complex nature in
which CRDE performs searches, t cannot be changed during the course of
a query. CRDE functions which attempt to alter t will return a -152,
or "table may not be modified", error. CRDE functions which can
change a table are listed below:
tadd tchange tchangeif tchcol
tdelete tdeleteif tdropindex tempty
tindex tinsert tload treplace
tsubtract tupdate
In addition, you may not close or drop table in read-only mode. This
is also the only case in which if t is a TEMP table, it will not be
automatically dropped by any CRDE functions called in action (TEMP
tables and auto-cleanup are described in full detail in Chapter 10).
The following example, although meaningless, demonstrates the abilities
and limitations within an if function.
TABLE *sups, *answer;
int iffunc(void *rec)
{
TABLE *t;
double d;
/* these function calls are ok, do not change sups */
t = tselect("test", sups, "s#", EQ, 100, NULL);
tdrop(t);
taverage(sups, "status", &d);
/* illegal to change sups within iffunc */
t = tdelete(sups, "status", GT, 20, NULL);
/* cannot close sups within iffunc */
tclose(sups);
return 1;
}
answer = tselectif("answer", t, iffunc, NULL);
- 34 -
As you can see, "..if" functions provide almost unlimited querying
capabilities. You might think the these functions could replace
their regular counter parts because you can do everything a search
expression can do inside of action. However, there are several
reasons why not to:
- the most obvious is that "..if" functions require you to write
a user-defined function for every query.
- CRDE can only take advantage your indexes if you specify a
search expression. For this reason you should always put as
much of the query in the search expression as you possibly
can.
- and since action must receive a copy of every record for
evaluation, "..if functions" are slightly slower than their
regular counterparts.
Some queries, however, cannot be performed without an "..if" function.
The full capabilities of "..if" functions are demonstrated in Chapter
11, Performing Queries.
- 35 -
Chapter 8
Miscelleneous Operations
Restructuring tables
Like most data management systems (good ones anyway), CRDE provides
a way to restructure tables, without having to unload and reload all
the table's information.
Unlike other libraries which require you to use a special utility to
restructure a table, CRDE provides the trestruct function. With the
trestruct function you can
- add columns to a table
- remove columns from a table
- reorder columns in a table
- change column types and sizes
- change the primary key
trestruct is declared in crde.h as
TABLE *trestruct(char *name, TABLE *t, char *td);
trestruct returns a restructed version of t based upon td. td should
be a table descriptor like the one described in Chapter 3. trestruct
interprets the columns in td as follows:
- If the column name does not appear in t then it is added to
the table and filled with some default value (like 0 or "")
depending upon the datatype.
- If the column does appear in t and has the same type, then all
data from the original column is copied into the new column.
- If the column appears in t but has a different type, trestruct
converts all data in the original column to the type of the new
column.
- Any columns in the original table t, which do not appear in
td are not included in the restructured table.
- 36 -
Data conversion
trestruct can convert any data type to any other data type except for
dates. Date columns can only be converted to char columns and only
char columns can be converted into dates. When converting data to
char columns, trestruct converts the value into a string and copying
it into the new column. f columns will have 4 decimal places, $
will have 2. Dates are formatted as "mm/dd/yyyy". If the string is
too large to fit in the new column it will be truncated automatically.
When converting char types to other data types, the char column must
hold be valid representation of the new value. If it isn't then the
column will be initialized as described above. Data converted to f
and $ columns may contain any number of decimal places. Data
converted to dates must have the format "mm/dd/yyyy". If a value
being converted to a numeric data type ( i, l, f, $ ) is out of
range for that type, the converted value is undefined.
For following example uses trestruct to create a new sups table:
t = trestruct("restruct", sups, "s# l, sname c16, city c16,"
"first d");
- "s#" is converted to type l.
- "sname" and "city" are shortened.
- "status" does not appear in the table descriptor and so is
not included in the new table.
- The "first" column is added to the table. It contains the
date the first order was placed to that supplier.
trestruct returns a restructed copy of the sups table. sups is still
intact. To replace sups with the new table, you would have to erase
the old sups and replace it with the new table.
tdrop(sups);
tclose(t);
trename("restruct", "sups");
Renaming columns
You can change a column's name with the tchcol function. tchcol is
declared in crde.h as
int tchcol(TABLE *t, char *old, char *new);
tchcol renames column old to new. The type or size of the column remains
unchanged.
- 37 -
Getting info from your tables
CRDE provides several functions for getting information about the
structure and properties of a table.
trows Returns the number of rows in a table.
tcols Returns the number of columns in a table.
tcoltype Returns the type of a column.
tcolsize Returns the size of a column, in bytes.
trowsize Returns the size of a row, in bytes.
tindexes Returns the number of secondary indexes on a
table.
tkeyed Returns 1 if the table has a primary key or 0
if not.
tstruct Returns a table containing information about the
structure of a table.
tkeys Returns a table containing information about the
secondary indexes on a table.
Of these, only tstruct and tkeys bear much detail. The others are
relatively self-explanatory. tstruct is declared in crde.h as
TABLE *tstruct(char *name, TABLE *t);
tstruct returns the structure of a table, as a table. The result is a
table which is equivalent to
tcreat(name, "name c18,type c6").
Column names and types appear in the struct table in the order that
they were declared. Columns which are part of the primary key have
their names prepended with an asterisk '*'. Using tstruct on the sups
table would produce the following table:
name type
----------------------- ------
*s# i
sname c26
city c26
status i
Altering the table produced by tstruct has absolutely no effect upon the
structure of the table passed to tstruct.
tkeys is a similar function for obtaining information about a table'
s secondary indexes. tkeys produces a table equivalent to
tcreat(name,"ext c3, key c144").
- 38 -
The ext column contain the indexes' extension on disk, "s0" - "s7".
The key column contains the index descriptor which created the
secondary index. To illustrate, suppose the sups table had a two
secondary indexes created by
tindex(sups, "sname");
tindex(sups, "city,status");
Calling tkeys on sups would return the table
ext key
------- -------------------------
s0 sname
s1 city,status
Importing and Exporting data
CRDE provides functions for importing and exporting data to other
file formats. CRDE currently supports the ascii and dBASE file formats.
Since most other data managers support either one or both of these
types, you should have no problem converting CRDE tables to and from
virtually any other format.
Ascii files.
CRDE supports comma-delimited ascii compatible files. CRDE can import
an ascii file with its timportascii function. timportascii is
declared in crde.h as
TABLE *timportascii(char *name, char *td, char *source);
name is the name of the table which will be created. source is the
complete pathname (including extension) of the ascii file to be
imported. td is a table descriptor like the one described in Chapter
3, and may include a primary key. It should describe what the
resulting table will look like. This is necessary because an ascii
file contains no details about the types or sizes of the columns it
contains.
timportascii requires that each column in a row be separated by a
comma, and that each row exist on a single line of the test file.
timportascii reads data from the ascii file according to the table
descriptor you define. The data in the ascii file must be formatted
accordingly for each type of CRDE column.
CRDE
type Expected format of column in ascii file
c A character string contained in quotes "". If the string is
too large for the column, it will be truncated to fit.
i A base 10 integer is expected. Negative values should be
preceded with a negative sign -. If the integer is out of
range for the column, the value for that column is undefined.
- 39 -
l A base 10 integer is expected. Negative values should be
preceded with a negative sign -. If the integer is out of
range for the column, the value for that column is undefined.
d A date in the format of mm/dd/yyyy is expected. The date must
be enclosed in quotes. Whitespace is ignored.
f A floating point value with any number of decimal places.
Exponential notation may be used, +/-eNN. Negative numbers
must be preceded with a negative sign -. If the number is
out of range, the resulting value imported is undefined.
$ Same as f.
Any whitespace between columns, and rows, is ignored by timportascii.
You can export a CRDE table to an ascii file with the texportascii
function. texportascii is declared in crde.h as
long texportascii(TABLE *t, char *dest);
t is the table to be exported. dest if the filename of the ascii file
to create. Every column in the file will be separated by a comma, and
each row will appear on an individual line. Output is formatted
depending upon datatype.
CRDE
type Format of column in ascii file
c CRDE will output a character string enclosed in quotes "".
i CRDE will output a base 10 integer. Negative numbers will be
preceded with a negative sign -.
l CRDE will output a base 10 integer. Negative numbers will be
preceded with a negative sign -.
d Dates are output in the format mm/dd/yyyy and enclosed in quotes.
f Floating point numbers are output with 4 decimal places.
Negative number are preceded with a negative sign.
$ Dollar values are output with 2 decimal places. Negative numbers
are preceded with a negative sign.
texportascii returns the number of rows successfully exported.
- 40 -
dBASE files
CRDE supports the dBASE III and dBASE III Plus file formats. You can
import a dBASE file into a CRDE table with the timportdBASE function.
timportdBASE is declared in crde.h as
TABLE *timportdBASE(char *name, char *source);
Importing a dBASE file is somewhat simpler than importing an ascii file
since no column information is required. timportdBASE automatically
reads the dBASE file header for information about field types and
lengths and creates the CRDE table structure for you. When converting
dBASE field types to CRDE column types, CRDE uses the smallest CRDE
column type which can correctly hold all legal values for the dBASE
field. timportdBASE make the following translations when converting
a dBASE file to a CRDE table.
dBASE
type How dBASE field is converted to CRDE
C c column equaling length of dBASE field + 1.
D dBASE's dates are converted to CRDE's date format.
N If the number of decimal places equals 2 then the colum type
is $. If decimal places is > 0 then column is type f. If the
length of the number field is > 11 then the column is type f.
If the length of the number field is < 7 then the column is
type i. Otherwise the column is type l.
L Logical fields are converted to type i. If the field is 'Y' or
'T' the column will have a value of 1, otherwise 0.
M CRDE does import memo fields. Any memo fields in the dBASE
file are ignored.
You can export to a dBASE file with the texportdBASE function.
texportdBASE is declared in crde.h as
long texportdBASE(TABLE *t, char *dest);
texportdBASE creates the dBASE header for you by converting CRDE
columns to uppercase and creating dBASE field types which are
equivelent to each CRDE column. texportdBASE make the following
translations when converting CRDE tables to the dBASE format.
- 41 -
CRDE
type How CRDE column is converted to dBASE
c Character field with a length equaling the length of the c
column - 1.
i Numeric field with a length of 6 and 0 decimal places.
l Numeric field with a length of 11 and 0 decimal places.
d CRDE dates are converted to dBASE format.
f Numeric field with a length of 19 and 4 decimal places.
$ Numeric field with a length of 19 and 2 decimal places.
- 42 -
Chapter 9
Working with Dates
The date_t type and date math
CRDE introduces a new date type, date_t, and 15 functions to create
and manipulate dates.
d columns in CRDE tables are of type date_t. date_t is declared in
date.h as
typedef long date_h;
CRDE represents dates by assigning a date as the number of days since
1/1/0001. The advantage of storing dates this way is that you don't
need to resort to special functions to add, subtract or compare dates
because you can use C's built in mathmatical abilities. For example,
to add 7 days to a date you would simply add 7 to the date_t value
d = d + 7;
To find the difference between 2 dates, you simply subtract them.
dif = d2 - d1;
You can compare dates just like any other integer types. To test if
two dates are equal, use then == operator.
if (d1 == d2) { /* ... */ }
You can just as easily use the >, < >=, <= and != operator when
comparing dates.
Although storing dates as an integer makes them easy to work with in
a mathematical sense, its not very meaningful to say that you received
an order 729172 days after 0 a.d. Thus CRDE's date functions provide
a way to easily convert dates to/from standard calender notation.
Converting calender dates
The mkdate function is used to convert a calender date into the date_t
type. mkdate is declared in date.h as
date_t mkdate(int month, int day, int year);
- 43 -
To convert the date 5/6/1968 to date_t would be
date_t d;
d = mkdate(5, 6, 1968);
You must remember to use the century in front of the year. A date like
d = mkdate(5, 6, 68);
would return a valid date_t date, for May 5, 0068, not May 5 1968!
mkdate knows about calender months, leap years, and leap centuries.
It will return -1 if the date passed to it is invalid: like 2/29/1990.
Similarly, you can convert a date_t type to a calender date with the
gmdate function. gmdate is declared in date.h as
int gmdate(date_t d, int *month, int *day, int *year);
To extract the month, day and year from a date_t type would look
something like
int month, day, year;
gmdate(d, &month, &day, &year);
You can extract the day, month, and year individually from a date_t
with the day, month, and year functions respectively:
int day(date_t d);
int month(date_t d);
int year(date_t d);
Example:
d = mkdate(11, 2, 1986);
printf("%d/%d/%d", month(d), day(d), year(d));
The output to the above printf function is
11/2/1986
Miscellaneous date functions
Not all of CRDE's date functions work directly with the date_t type.
To determine if a year is a leap year, you can use the leapyear
function. A related function, daysinyear, returns the number of days
in a given year.
int leapyear(int year);
int daysinyear(int year);
- 44 -
To find how many days have past since the beginning of the year, you
can use the dayofyear function. dayofyear is declared in date.h as
int dayofyear(int month, int day, int year);
dayofyear returns the number of days past for that date of that year.
dayofyear has a complement function which takes the number of days and
returns the appropriate month and day. monthday is declared as
int monthday(int days, int year, int *month, int *day);
Given the year and total number of days past, monthday can determine the
month and day.
Month and year math
Most data managers allow you to add and subtract days from dates. But
few offer the ability to add by months or years. Adding by months or
years is complicated by the fact that all months and years do not
contain the same number of days. A working, but hardly adequate,
substitute used by some DBMS's is to add and subtract multiples of
30 when adding months or multiples of 365 when adding years. But
suppose you wanted to collect a payment one month from July 1, 1990?
Adding 30 days would result in July 31, 1990, not August 1, 1990 as
expected.
CRDE provides functions for adding both months and years to a date.
They are declared in date.h as
date_t monthadd(date_t d, int months);
date_t yearadd(date_t d, int years);
The monthadd function accepts a date and returns a date with months
months added to it. monthadd correctly adds months. Adding 1 month
to July 1, 1990 yields August 1, 1990. You can subtract months from
a date by simply making the months parameter negative. yearadd works
in a similar fashion. yearadd returns a date +/- the number of years
specified.
Day of week functions
Often times when using dates, the day of week can be significant.
For example, you might want to only allow orders to be shipped during
the week, and not on the weekend. CRDE provides two useful functions
which use the day of week. dayofweek returns the day of week of the
date specified. dayofweek is declared in date.h as
int dayofweek(date_t);
dayofweek returns a constant between 0 and 6 which stand for the days
of the week between Sunday and Saturday. To help the translation, has
constants declared in date.h for every day of the week.
- 45 -
#define SUNDAY 0
#define MONDAY 1
#define TUESDAY 2
#define WEDNESDAY 3
#define THURSDAY 4
#define FRIDAY 5
#define SATURDAY 6
firstday is a useful function to determine the date of the first
Monday, Tuesday, Wednesday, etc. of the month. firstday is declared
in date.h as
date_t firstday(int month, int year, int dayofweek);
Suppose you wanted an order to be placed on the first Monday of
January,1990. You could easily accomplish this with firstday
d = firstday(1, 1990, MONDAY);
firstday returns the date as a date_t type. MON is a constant for a
day of the week as described above. firstday is useful in many
applications. You can also use firstday for calculating the second,
third, and fourth Monday, Tuesday, etc. of a month by simply
calculating the first day and adding the appropriate number of weeks.
For example, the following function returns the date of the fourth
thursday of any month of any year:
date_t FourthThu(int month, int year) {
int i;
i = firstday(month, year, THURSDAY);
return i >= 0 ? i + 21 : i;
}
- 46 -
Chapter 10
Temporary Tables
STATIC tables
The concept of a temporary table is very important in CRDE. Unlike
in other RDBMS's temporary tables have special meaning in CRDE.
Temporary tables can be useful in a number of situations,
especially in queries.
In CRDE you can specifically designate a table as temporary. There
are actually two types of temporary tables in CRDE, the first of
which is STATIC. To specify a table as a temporary table, simply
use STATIC instead of a regular table name like "sups". For example:
answer = tcreat(STATIC, "*s# i, sname c26, city c26, status i");
STATIC is defined in crde.h. You can use STATIC wherever you would
use a normal table name.
t = tcreat(STATIC, "*s# i, sname c26, city c26, status i");
t = tselect(STATIC, sups, "city", EQ, "Boston", NULL);
Using STATIC relieves the programmer from having to give temporary
tables some unique name. CRDE automatically generates a unqiue name
for the table which is not seen by the user. Temporary tables can be
distinguished by TABLE * each is assigned to. You may open any number
temporary tables, as long as the total number of tables does not
exceed 128.
Virtual tables
Another advantage of CRDE's temporary tables is that CRDE can create
and maintain them completely in memory. This has several advantages,
the greatest of which is speed. The overhead of creating a file(s)
for the table is removed, which also means that no file handles are
used by the temporary table. All reads and writes to the table go
directly to memory so any operations performed on it are extremely
fast. Sometimes a temporary table may be too big to fit in memory, or
it might be hogging so much memory that CRDE cannot perform its other
operations efficiently. When this happens, CRDE will place part or all
of the table on disk to release memory. This technique is called
transparent migration. CRDE will assign the table a unique table name
and flush it to disk. The important thing is that CRDE handles all the
details in managing temporary tables for you. You never need to worry
about whether a temporary table is on disk, in memory, or both. You
don't even have to worry cleaning up afterwards. When you close the
temporary table, the image on disk (if any) is removed automatically.
- 47 -
Temporary files created by CRDE will have the format "~TMPXXXX " where
XXXX is a hexadecimal number ranging from 0x0 to 0xFFFF. Normally,
these files are deleted automatically for you by CRDE. Sometimes,
however, you may find one or more of the lying around on your disk.
This can happen is you forget to close a temporary table or your
program terminates abnormally. You can erase these files since they
are no longer used by CRDE.
Despite all these special abilities, CRDE places no restrictions on
what you can do with temporary tables. You can use a temporary table
any place you would use a regular one. You can query them, update them,
create indexes on them, and even perform transactions on them.
TEMP tables and automatic cleanup
The second type of temporary table is the TEMP table. TEMP tables are
identical to STATIC tables in every way except one. TEMP tables support
a feature called auto-cleanup, which means if you use it correctly, you
don't even need to specifically close the table when your through with
it. CRDE will know when you are through with it and will close (and
delete) it for you automatically.
When and how would you want to use this feature? Well maybe an example
would better illustrate the point. Suppose you use tselect to select
all the rows from the sups table where city EQ "Boston"? You also
want it sorted by the supplier's name.
t = tselect("temp1", sups, "city", EQ, "Boston", NULL);
r = tsort("temp2", t, "sname");
tdrop(t);
Notice how this requires three steps. Notice how you must remember
to drop t. Otherwise it would just lie around wasting memory. Now
lets try declaring t as a TEMP table.
t = tselect(TEMP, sups, "city", EQ, "Boston", NULL);
r = tsort("temp", t, "sname");
Notice the tdrop(t) statement is gone. Because t is TEMP, it was
automatically dropped by tsort for you.
But here's where the real advantage of TEMP tables shines. We can
save another step by nesting the tselect statement within tsort!
r = tsort(STATIC,
tselect(TEMP, sups, "city", EQ, "Boston", NULL),
"sname"
);
There is no limit to the number of TEMP tables which may be nested
in this manner.
- 48 -
What happens if you nest a table which is not a TEMP table? Well
since its not a TEMP table it won't be automatically dropped by the
function which receives it. And because you have no access to it
(its not assigned to any variable), it will simply lie around for
the duration of your program, using up memory. Eventually, CRDE will
realize that the table is not being used and will release as much
memory from the table as it possibly can. However, there will always
be a small piece of the table (usually about 1k) which will remain in
memory until the program terminates.
Rules for using TEMP tables
Which functions support auto-cleanup of TEMP tables? Practically all
of them. You can determine if a table will be automatically dropped
by a CRDE function by following this simple rule:
A CRDE function will automatically drop a TEMP table passed
to it as a parameter if the function does not or can not
alter the table in any way. If the TEMP table cannot be thus
affected by the function, it will be automatically dropped when
the function completes, even in the event of an error.
Altering a table means inserting, changing, or deleting rows or columns
in the table. Here is a list of all CRDE functions which can alter
tables:
tadd tchange tchangeif tchcol
tdelete tdeleteif tdropindex tempty
tindex tinsert tload treplace
tsubtract tupdate
For every rule, there are always exceptions. Some CRDE functions will
not automatically drop a TEMP table, regardless of whether they alter
it. These functions are generally those ones related to transaction
tracking and memory management.
tcommit tflush tnormal tmark
tmode trelease trollback ttransact
twritec
If a CRDE function accepts two tables as parameters, it will drop
either of them if they are TEMP. However, a TEMP table in read-only
mode will not be dropped automatically by any CRDE function. This can
only occur if the TEMP table is used in one of CRDE's "..if" functions.
The table will not be automatically dropped by any CRDE function calls
in the user-defined function specified in the "..if" function.
You will find using both TEMP and STATIC tables invaluable in your
applications. Use TEMP whenever you nest a CRDE function which returns
a table, inside another CRDE function, otherwise use STATIC.
- 49 -
Chapter 11
Performing Queries
What is a query?
A query is a question that you ask about the information in your
database. You might want to find out which suppliers are supplying
a certain part, or which how many red parts are currently on order,
or how many suppliers supply both bolts and nuts, etc. Queries have
many uses in applications. Queries are usually used to answer questions,
create lookup or view tables, make calculations, or generate reports.
CRDE contains very powerful querying capabilities, matching those found
in high level querying languages such as SQL. Because it is rooted in
a procedural language however, CRDE has abilities which range far
beyond the capabilities of other relational languages. CRDE provides
the tools to create queries as complex as you could possibly imagine.
Developing queries in any relational language is an art form of sorts,
and doing so in CRDE is no exception. This chapter attempts to look
at many different types of queries, and CRDE's approach to each. To
assist you, each query is first described in English. The CRDE answer
of the query will appear next followed by an equivalent SQL query.
This will help you more quickly understand the nuances of how to create
CRDE queries. The answer to each query is also shown.
Each of the following queries are just small portions of code and not
meant to be entire programs. All assume that the following declarations
have been made:
TABLE *sups, *parts, *orders, *answer, *set;
typedef struct {
int s;
char sname[26];
char city[16];
int status;
} sup;
typedef struct {
int p;
char pname[16];
char color[11];
} part;
- 50 -
typedef struct {
int s;
int p;
int qty;
} order;
sups = tcreat(STATIC, "*s# i, sname c26, city, c16, status i");
tload(sups, 1, "Smith, John", "Boston", 5);
tload(sups, 2, "Jones, Ben", "New York", 10);
tload(sups, 3, "Barnes, Frank", "Boston", 15);
tload(sups, 4, "Landon, Mark", "Auburn Hills", 20);
tload(sups, 5, "Andrews, Jim", "New York", 5);
parts = tcreat(STATIC, "*p# i, pname c16, color c11");
tload(parts, 1, "bolt", "red");
tload(parts, 2, "screw", "red");
tload(parts, 3, "screw", "blue");
tload(parts, 4, "nut", "gray");
tload(parts, 5, "bolt", "gray");
tload(parts, 6, "nail", "red");
orders = tcreat(STATIC, "*s# i, *p# i, qty i");
tload(orders, 1, 1, 100);
tload(orders, 1, 2, 200);
tload(orders, 1, 3, 300);
tload(orders, 1, 4, 400);
tload(orders, 1, 5, 500);
tload(orders, 1, 6, 600);
tload(orders, 2, 1, 100);
tload(orders, 2, 2, 100);
tload(orders, 2, 6, 100);
tload(orders, 3, 1, 200);
tload(orders, 3, 2, 50);
tload(orders, 4, 3, 100);
tload(orders, 4, 5, 500);
/*
sups
s# sname city status
------- --------------- --------------- -------
1 Smith, John Boston 5
2 Jones, Ben New York 10
3 Barnes, Frank Boston 15
4 Landon, Mark Auburn 20
5 Andrews, Jim New York 5
- 51 -
parts
p# pname color
------- --------------- -----------
1 bolt red
2 screw red
3 screw blue
4 nut gray
5 bolt gray
6 nail red
orders
s# p# qty
------- ------- -------
1 1 100
1 2 200
1 3 300
1 4 400
1 5 500
1 6 600
2 1 100
2 2 100
2 6 100
3 1 200
3 2 50
4 3 100
4 5 500
*/
Simple Queries
Simple Select Queries
The majority of queries will involve selecting rows from a single
table based on one or more conditions. These types of queries can
be answered with the tselect function. tselect is very much like
using an SQL SELECT statement with the * operator. tselect, however,
is limited to querying only a single table at a time. Suppose you
wanted find all the suppliers who live in Boston.
answer = tselect(STATIC, sups, "city", EQ, "Boston", NULL);
SELECT * FROM SUPS WHERE CITY = "Boston"
s# sname city status
------- --------------- --------------- -------
1 Smith, John Boston 5
3 Barnes, Frank Boston 15
- 52 -
Note: unless you want to keep the results of a query permanent,
which is rare, you will probably want all of your queries to be of
type STATIC. Queries nested inside of other queries should always
be of type TEMP.
CRDE can also create compound expressions by adding more conditions
to the search expression. You may only want those suppliers who live
in Boston and have a status greater than 5.
answer = tselect(STATIC, sups, "city", EQ, "Boston",
"status", GT, 5, NULL);
SELECT * FROM SUPS WHERE CITY = "Boston" AND STATUS > 5
s# sname city status
------- --------------- --------------- -------
3 Barnes, Frank Boston 15
ORing queries
Notice that CRDE can handle queries in which the conditions are
logically anded together in a fairly straightforward manner. But
suppose you wanted a list of all suppliers who live in Boston or
have a status > 5? There are several ways to handle this. The
simplest is to break up the query into 2 separate queries and union
them together with the tunion function.
answer = tunion(STATIC,
tselect(TEMP, sups, "city", EQ, "Boston", NULL),
tselect(TEMP, sups, "status", GT, 5, NULL)
);
SELECT * FROM SUPS WHERE CITY = "Boston" OR STATUS > 5
s# sname city status
------- --------------- --------------- -------
1 Smith, John Boston 5
2 Jones, Ben New York 10
3 Barnes, Frank Boston 15
4 Landon, Mark Auburn 20
tunion is declared in crde.h as
TABLE *tunion(char *name, TABLE *t1, TABLE *t2);
tunion returns a table containing all the rows found in t1 and t2.
t1 and t2 must be compatible, i.e. they must be identical on a column
by column basis in both type and size. They may have different
primary and/or secondary keys however. For example, the following
tables would all be compatible with one another
- 53 -
tcreat(STATIC, "s# i, sname c26, city 26, status i");
tcreat(STATIC, "*s# i, sname c26, city c26, status i");
tcreat(STATIC, "sup# i, name c26, loc c26, stat i");
while these are not
tcreat(STATIC, "s# i, sname c26, city, c26, status i");
tcreat(STATIC, "s# i, sname c26, city c26");
tcreat(STATIC, "s# i, sname c16, city c26, status i");
tcreat(STATIC, "s# l, sname c26, city c26, status i");
The structure of the resulting table will be identical to t1, and will
have the same primary key, if any. This is important to remember
because tunion will may return two different tables depending upon
whether t1 is keyed.
In this particular query, tunion is only effective if sups has a
primary key, which in this case it does. If sups is not keyed, a
problem can arise if there exist rows in the table where a supplier
lives in Boston and has a status > 10. If this is true, then both
the innermost tselect statements will collect the same row. The
difference is that when sups is keyed, the resulting union, which is
also keyed, filters out any duplicate rows automatically. If sups were
not keyed, then any rows in which the supplier lives in Boston and has
a status > 10 would appear twice in the answer table.
This is just one in many cases where the existence, or absence, of a
primary key can affect the result of a query. You need to keep this
in mind when designing your tables and the queries you will need to
perform on them.
Another way to perform the query would be to use tselectif and write
a simple "..if" function to perform the query.
int iffunc(void *rec) {
return strcmp(((sup *)rec)->city, "Boston") == 0
|| ((sup *)rec)->status > 5;
}
answer = tselectif(STATIC, sups, iffunc, NULL);
Either is an acceptable answer to the query.
Projecting columns
CRDE gives you the ability to project columns from a table with its
tproject function. tproject is much like tselect except it specifies
only those columns which it wants to display. For example, if you
wanted a list of all supplier names and the city they live in.
answer = tproject(STATIC, sups, "sname,city", NULL);
SELECT SNAME, CITY FROM SUPS
- 54 -
sname city
--------------- ----------------
Smith, John Boston
Jones, Ben New York
Barnes, Frank Boston
Landon, Mark Auburn
Andrews, Jim New York
In CRDE however, it is generally more common to select rows rather than
to project them. Usually you won't bother to remove those columns
which you don't really need in queries except in special occasions.
However, there is one type of query where tproject shines. That is
when selecting distinct values from a column.
Selecting distinct values
tproject is primarily used to find distinct values in a column.
tproject expects a special type of column list which allows you to set
the primary key of the resulting table by prepending column names with
an asterisk '*', similar to a table descriptor. The result of this is
that all duplicate values in the column(s) marked as part of the
primary key will be filtered out automatically by CRDE when the new
table in generated, since no two rows in a table may have the same key
values.
Suppose you wanted a list of all supplier #'s who have made orders.
Several supplier #'s exist more than once in the orders table.
However, by defining the answer table to be "*s#" any duplicates
are filtered out automatically.
answer = tproject(STATIC, orders, "*s#", NULL);
SELECT DISTINCT S# FROM ORDERS
s#
-------
1
2
3
4
Joining tables
The heart of any relational language is the ability to relate
information between tables. In relational terms, this is called
"joining" tables. You can join tables with CRDE's powerful tjoin
function. The orders table consists of three columns s#, p#, and qty.
Suppose we wanted a list of all orders which included all the details
about the supplier making the order. All the information about
suppliers exists in the sups table. By using tjoin, you can merge the
information in sups and orders together into a single table.
- 55 -
answer = tjoin(STATIC, sups, "s#", orders, "s#",
"s#,sname,city,status,p#,qty");
SELECT S#,SNAME,CITY,STATUS,P#,QTY FROM SUPS,ORDERS WHERE
SUPS.S# = ORDERS.S#
s# sname city status p# qty
------- --------------- --------------- ------- ------- -------
1 Smith, John Boston 5 1 100
1 Smith, John Boston 5 2 200
1 Smith, John Boston 5 3 300
1 Smith, John Boston 5 4 400
1 Smith, John Boston 5 5 500
1 Smith, John Boston 5 6 600
2 Jones, Ben New York 10 1 100
2 Jones, Ben New York 10 2 100
2 Jones, Ben New York 10 6 100
3 Barnes, Frank Boston 15 1 200
3 Barnes, Frank Boston 15 2 50
4 Landon, Mark Auburn 20 3 100
4 Landon, Mark Auburn 20 5 500
tjoin is declared in crde.h as
TABLE *tjoin(char *name, char *cl1, TABLE *t1, char *cl2, TABLE
*t2, char *pl);
tjoin joins t1 and t2 to create a new table. cl1 and cl2 are column
lists describing the join columns for each table. Up to 256 columns
may be specified. tjoin joins those rows from either table which have
identical values in their corresponding join columns. Join columns
must be identical in both type and size on a column by column basis:
c columns must be joined to c columns, i columns must be joined to i
columns, etc.
pl lists those columns to be included in the result table. pl may
contain from both t1 and t2. A problem might occur however, if there
exists a column in both t1 adn t2 with the same name. You could not
include both in the result table because CRDE does not allow tables
with duplicate column names. If you specify one of the columns, CRDE
will pick the one from t1 by default. You should take this into
consideration when creating tables you may plan to join later.
Like tproject, tjoin's column list may define a primary key. You can
use this to remove duplicate values from the result table. For
example, suppose you wanted a list of supplier's names who have
placed orders.
answer = tjoin(STATIC, sups, "s#", orders, "s#", "*s#,sname");
SELECT DISTINCT S#, SNAME FROM SUPS, ORDERS
- 56 -
s# sname
------- ----------------
1 Smith, John
2 Jones, Ben
3 Barnes, Frank
4 Landon, Mark
Order by queries
Often you may want to sort a query in a particular order for viewing
or reporting. Whenever you perform a query in CRDE, using tselect,
tjoin, or any other function which returns a table as a result, the
order of the rows in the answer table is undefined. If you want to
put them in order you must use the tsort function to sort the answer
table. Remember the first query where we wanted all the suppliers
who lived in Boston. Suppose we want that list in alphabetic order
by the supplier's name. You can do this by simply nesting the query
inside a tsort function call.
answer = tsort(STATIC,
tselect(TEMP, sups, "status", GT, 5, NULL),
"sname"
);
SELECT * FROM SUPS WHERE STATUS > 5 ORDER BY SNAME
s# sname city status
------- --------------- --------------- -------
3 Barnes, Frank Boston 15
2 Jones, Ben New York 10
4 Landon, Mark Auburn 20
tsort is declared in crde.h as
TABLE *tsort(char *name, TABLE *t, char *id);
tsort returns a sorted copy of t according to the index descriptor in
id. You can sort a column in descending order by prepending its name
with an exclaimation point '!'. For example, to sort the above query
in descending order would look like
answer = tsort(STATIC,
tselect(TEMP, sups, "city", EQ, "Boston", NULL),
"!sname"
);
SELECT * FROM SUPS WHERE STATUS > 5 ORDER BY SNAME DESC
- 57 -
s# sname city status
------- --------------- --------------- -------
4 Landon, Mark Auburn 20
2 Jones, Ben New York 10
3 Barnes, Frank Boston 15
tsort should always be the outermost functions call in a query.
Statistical queries, making calculations
CRDE has special functions to calculate the count, min, max, average,
and sum of a column. These are tcount, tmin, tmax, taverage, and tsum
respectively. Unlike in SQL, CRDE stores the result of these queries
in a variable, not a table.
All of CRDE's statistcal functions have the same declaration:
int statfunc(TABLE *t, char *c, void *result);
where statfunc is tcount, tmin, tmax, taverage, or tsum. t is the table
being queried. c is the column to perform the statiscal analysis on.
result is a pointer to a buffer which will hold the result of the query.
The result type is dependent upon the function you are using and the
datatype of the column being queried. statfunc returns 0 if the query
was successful.
Find the maximum status of any supplier.
int i;
tmax(sups, "status", &i);
SELECT MAX(STATUS) FROM SUPS
max(status)
20
CRDE places the result of tmax into i, which must be the same type as
the column being queried, int. Not all of the statistical query
functions expect a result type to be the same as the column's type
however. For example, tsum and taverage always expect the result to be
of type double, regardless of the column type.
double d;
tsum(orders, "qty", &d);
SELECT SUM(QTY) FROM ORDERS
- 58 -
sum(qty)
3250
You can take statistical queries of a given subset of a table by simply
nesting a query within the statistical function. For example, to find
the maximum status of any supplier who lives in Boston
int i;
tmax(tselect(TEMP, sups, "city", EQ, "Boston", NULL),
"status", &i);
SELECT MAX(STATUS) FROM SUPS WHERE CITY = "Boston"
max(status)
15
You might be wondering what tmax would return if the table it is
querying is empty. You can't find the maximum of nothing. In a truly
relational language, the result of such a query would be NULL, or
unknown. However, since CRDE does not support NULL values, it returns
an error code instead, specifying that it could not answer the query.
Set-related queries
CRDE provides two very powerful set-comparison functions: tdiff and
tintersect. They are declared in crde.h as
TABLE *tdiff(char *name, TABLE *t1, TABLE *t2);
TABLE *tintersect(char *name, TABLE *t1, TABLE *t2);
tdiff creates the relational difference between t1 and t2. The
resulting table will contain all those rows in t1 which are not found
in t2. tintersect is just the opposite. It returns a table containing
all the rows in t1 which are also found in t2. Like tunion, t1 and t2
must have compatible structures, i.e. they must be identical in both
type and size on a column by column basis. The resulting table will
have a structure identical to t1.
This query uses tdiff to determine which suppliers have not made any
orders.
answer = tdiff(STATIC,
tproject(TEMP, sups, "s#", NULL),
tproject(TEMP, orders, "*s#", NULL)
);
s#
-------
5
- 59 -
Notice inside tdiff two sets are created: one containing every supplier #,
and one containing every supplier # who made an order. The resulting
difference leaves those supplier's who have not made any orders. Note
that the tables are compatible: both contain a single column of type i.
Although one table is keyed and one is not, tdiff does not complain.
Two tables do not have to have the same primary key to be compatible.
This query uses tintersect to find those parts ordered by both supplier
#1 and supplier #2.
answer = tintersect(STATIC,
tproject(TEMP, orders, "p#", "s#", EQ, 1, NULL),
tproject(TEMP, orders, "p#", "s#", EQ, 2, NULL)
);
Complex Queries
Subqueries
Suppose you wanted a list of all suppliers who have a status greater
than the average status.
double d;
taverage(sups, "status", &d);
answer = tselect(STATIC, sups, "status", GT, (int)d, NULL);
SELECT * FROM SUPS WHERE STATUS > (SELECT AVG(STATUS) FROM S)
s# sname city status
------- --------------- --------------- -------
3 Barnes, Frank Boston 15
4 Landon, Mark Auburn 20
Notice how d must be typecast (int) in the tselect statement because
the result of taverage is always double.
Correlated subqueries
A correlated subquery is a special type of subquery. In a correleated
subquery, the calculated subquery is dependent upon the current row
being queried. In SQL, this type of query requires that you use an
alias. In CRDE it requires and "..if" function.
This query returns all suppliers which have a status greater than
the average status for that supplier's particular city.
- 60 -
int iffunc(void *rec) {
double d;
int i;
i = taverage(
tselect(TEMP, sup, "city", EQ, ((sup *)rec)->city, NULL),
"status", &d
);
return i ? i : ((sup *)rec)->status >= d;
}
answer = tselectif(STATIC, sups, iffunc, NULL);
SELECT * FROM SUPS X WHERE STATUS >= (SELECT AVG(STATUS)
FROM S WHERE CITY = X.CITY)
s# sname city status
------- --------------- --------------- -------
2 Jones, Ben New York 10
3 Barnes, Frank Boston 15
4 Landon, Mark Auburn 20
CRDE uses the tselectif function to perform this query. Every row in
the table is passed to iffunc for evaluation. iffunc calculates the
average status of all suppliers for that row's city, then returns true
if the status for the row is greater than that average.
Multi-table joins
In relational databases multi-table (3 or more) joins are commonplace.
Although CRDE does not directly support multi-table joins, you can
simulate them using multiple tjoin calls. The technique for a three
table join is to join the first two tables together, and then join the
third table to the result. To perform a four table join, simply
perform a three table join, and then join the fourth table to the
result, and so on. There is no limit to the number tables which can
be joined in this fashion.
The following demonstrates the three table join by joining the sups,
parts, and orders table into a single table. The query is split into
two parts (instead of being nested) for clarity.
temp = tjoin(TEMP, parts, "p#", orders, "p#", "s#,p#,pname,qty");
answer = tjoin(STATIC, sups, "s#", temp, "s#",
"s#,sname,p#,pname,qty"
);
SELECT * FROM SUPS,PARTS,ORDERS WHERE SUPS.S# = ORDERS.S#
AND PARTS.P# = ORDERS.P#
- 61 -
s# sname p# pname qty
------- --------------- ------- --------------- -------
1 Smith, John 1 bolt 100
1 Smith, John 2 screw 200
1 Smith, John 3 screw 300
1 Smith, John 4 nut 400
1 Smith, John 5 bolt 500
1 Smith, John 6 nail 600
2 Jones, Ben 1 bolt 100
2 Jones, Ben 2 screw 100
2 Jones, Ben 6 nail 100
3 Barnes, Frank 1 bolt 200
3 Barnes, Frank 2 screw 50
4 Landon, Mark 3 screw 100
4 Landon, Mark 5 bolt 500
As in the two table join, it makes no difference in which order the
tables are joined. The result will always be the same.
"In" subqueries
Suppose you want a list of all suppliers who are supplying red parts.
This query requires two steps, first creating a set containing all red
parts, then selecting every supplier who supplies a part in the set.
You must remember to drop the set when the query is finished. You
cannot make set TEMP because it would be automatically dropped the
first time iffunc is called.
int iffunc(void *rec) {
return tlookup(set, "p#", EQ, ((order *)rec)->p, NULL);
}
set = tproject(STATIC, parts, "p#", "color", EQ, "red", NULL);
answer = tprojectif(STATIC, orders, "*s#", iffunc, NULL);
tdrop(set);
SELECT DISTINCT S# FROM ORDERS WHERE P# IN
(SELECT P# FROM PARTS WHERE COLOR = "red")
s#
-------
1
2
3
Multiple subqueries
To get the name of every supplier who is supplying red parts.
- 62 -
TABLE *set1, *set2;
int iffunc1(void *rec) {
return tlookup(set, "p#", EQ, ((order *)rec)->p, NULL);
}
int iffunc2(void *rec) {
return tlookup(set, "s#", EQ, ((sup *)rec)->s, NULL);
}
set1 = tproject(STATIC, parts, "*p#", "p#", EQ, "red", NULL);
set2 = tprojectif(STATIC, orders, "*s#", iffunc1, NULL);
tdrop(set1);
answer = tselectif(STATIC, sups, iffunc2, NULL);
tdrop(set2);
SELECT * FROM SUPS WHERE S# IN
(SELECT S# FROM ORDERS WHERE P# IN
(SELECT P# FROM PARTS WHERE COLOR = "red"))
s# sname city status
------- --------------- --------------- -------
1 Smith, John Boston 5
2 Jones, Ben New York 10
3 Barnes, Frank Boston 15
This query is simply a subquery within a subquery, more specifically,
and "in" subquery within an "in" subquery. Thus it requires two "..if"
function calls instead of just one.
Another way to do this would be to perform this query would be to take
the answer from the original "in" query, and join it to the sups table.
Either is a legitimate answer to the query.
Group By queries
CRDE performs group by queries with the tgroup function. tgroup
requires you to write a special group function which works with tgroup
to create the answer table. First, an example is shown to illustrate
what a common tgroup query will look like. Suppose you want a list of
the number of suppliers, per city.
- 63 -
int groupfunc(void *in, void *out, int first) {
typedef struct {
char city[26];
int count;
} ans;
switch (first) {
case 1 :
strcpy(((ans *)out)->city, ((sup *)in)->city);
((ans *)out)->count = 0;
case 0 :
((ans *)out)->count++;
break;
}
return 1;
}
answer = tgroup(STATIC, sups, "city", "city c26, count i",
groupfunc);
SELECT CITY,COUNT(S#) FROM S GROUP BY CITY
city count
--------------- -------
Auburn 1
Boston 2
New York 2
tgroup is declared in crde.h as
TABLE *tgroup(char *name, TABLE *t, char *cl, char *td,
int (*summary)(void *in, void *out, int first));
The cl parameter in tgroup lists the column(s) to group on. You can
group on up to 8 columns with tgroup. All rows in the table with the
same values in the columns in cl belong to the same group. In the
above example, rows are grouped by city. Thus all rows with the same
city belong to the same group.
td is a table descriptor describing the resulting table. td may be
anything you wish. summary is responsible for creating the table
described by td.
summary is a user-defined group function which actually generates the
result table. Group functions must follow a specific set of rules to
work correctly.
Writing grouping functions
summary should have a declaration similar to
int summary(void *in, void *out, int first);
- 64 -
Every row in each group is passed to summary and is referenced by in.
The information in used to calculate the output row for the group. out
is a pointer to the output row for a particular group. summary is
responsible for building out. first is a parameter used to determine
whether a row is the first or last element in the group. The
information is used to determine when to initialize the group and
whether to include the group in the answer table. The following
describes how tgroup uses summary to generate the answer table.
1. tgroup calls summary with first == 1. in points to the first
row in the group. summary should initialize out at this point.
2. tgroup calls summary with first == 0 for every other row in
the group. Each row is accessed through the pointer in.
summary should update any calculated columns in out based
on each row in the group.
3. After every row in the group has been passed to summary,
tgroup calls summary once more time with first == -1.
summary should perform any final calculations (now that all
rows in the group have been processed), and do any cleanup
necessary. out should now contain the values which will
appear in the answer table. However, summary can also decide
whether or not it wants to include out in the answer table.
If summary returns > 0 then tgroup will include out. If
summary returns 0 then tgroup will not include the row.
tgroup repeats step 1-3 for every group in the table. In steps 1. and
2., summary should return a value >= 0 if no errors occurred.
Regardless of the value of first, if the summary returns a value < 0,
tgroup aborts and returns NULL. terrno will be set to the value
returned by summary. A well designed summary should include error-
handling and perform any necessary cleanup if an error occurs.
To now describe the operation of the first example, tgroup groups the
table by "city". The resulting table will contain the city of the
group and the number of suppliers in that city ("city c26, count i").
tgroup then starts calling groupfunc for each row in each group to
generate the answer table.
groupfunc accepts the first row of the first group which happens to be
"Auburn". first equals 1 so control goes to the first case in the switch
statement
case 1 :
strcpy(((ans *)out)->city, ((sup *)in)->city);
((ans *)out)->count = 0;
out is initialized to "city "= "Auburn" and "count" = 0. Notice that
out is typecast with ans. ans must be a structure typedef which is
identical to the answer table being generated. in, of course, must be
typecast by sup.
Since in is also the first row in the group, we have to update "count"
in out. So control passes on to the next case in the switch statement
(since there is no break statement)
- 65 -
case 0 :
((ans *)out)->count++;
break;
case 0, which is used by every other row in the group besides the first,
increments the "count" in out. So "count" now equals 1. Control now
passes to the
return 1;
statement, and groupfunc returns successfully to tgroup.
tgroup normally would processes every other row in the group (all those
suppliers who live in Auburn) to groupfunc, with first set to 0.
groupfunc would then increment "count" in out for each row. However,
there happens to be only one row in the sups table who lives in Auburn,
so tgroup immediately calls groupfunc with first set to -1. groupfunc
simply returns 1 in this case, indicating that tgroup should insert out
should in the answer table. At this point, the answer table looks like
city count
--------------- -------
Auburn 1
tgroup now moves on to the next group, which happens to be "Boston".
The first row in the group (either John Smith or Frank Barnes,
whichever happens to be picked first by tgroup) is sent to groupfunc;
first of course, equals 1. out is initialized to "city" = "Boston",
"count" = 1. tgroup calls groupfunc again with the other row in the
group, with first set to 0. "count" is incremented to 2. groupfunc
is called once more with first set to -1, which immediatly returns and
the new out is inserted. The answer table now looks like
city count
--------------- -------
Auburn 1
Boston 2
tgroup repeats the process for the final group, "New York", which also
has two suppliers. After the final group is completed, tgroup returns
the generated table as its result.
city count
--------------- -------
Auburn 1
Boston 2
New York 2
- 66 -
Example #2.
Calculate the sum of all parts ordered by each supplier.
int calcsum(void *in, void *out, int first)
{
typedef struct {
int s;
int sum;
} group;
switch (first) {
case 1 :
((group *)out)->s = ((order *)in)->s;
((group *)out)->sum = 0;
case 0 :
((order *)out)->sum += ((order *)in)->quant;
break;
}
return 1;
}
answer = tgroup(STATIC, orders, "s#", "s# i, sumofqty i", calcsum);
SELECT S#,SUM(QTY) FROM ORDERS GROUP BY S#
s# sumofqty
------- -----------
1 2100
2 300
3 250
4 600
Example #2 is similar to the first example except that a column is
summed instead of counted.
Example #3.
This example demonstrate how the -1 flag in first can be used to find
a group average.
int calcavg(void *in, void *out, int first)
{
typedef struct {
int s;
double avg;
} group;
static int i;
switch (first) {
case 1 :
((group *)out)->s = ((order *)in)->s;
((order *)out)->avg = 0.0;
i = 0;
- 67 -
case 0 :
((group *)out)->avg += ((order *)in)->quant;
i++;
break;
case -1 :
((order *)out)->avg /= i;
break;
}
return 1;
}
answer = tgroup(STATIC, orders, "s#", "s# i, sumofqty i", calcavg);
SELECT S#,AVG(QTY) FROM ORDERS GROUP BY S
calcavg is identical to calcsum except that it contains the extra case
case -1:
((order *)out)->avg /= i;
break;
This calculates the average of the group by dividing the sum of all
the orders by the number of orders made. Note that this can only be
done by taking advantage of the fact that tgroup calls calcavg with
first == -1 at the end of each group.
As you can see, group functions are very similar to the if functions
described in Chapter 7, and provide an almost endless range of
possibilities. Like if functions, group functions place the host
table in a special read-only mode, so that you may not modify it.
However, no other restrictions are imposed by group functions.
Group Having queries
You can simulate the HAVING clause of an SQL statement by simply
placing the results of a tgroup within a tselect function. For
example, suppose you want only those rows generating in example #2
having a sum > 500.
answer = tselect(STATIC,
tgroup(TEMP, orders, "s#", "s# i, sumofqty i", calcsum),
"sumofqty", GT, 500, NULL
);
SELECT S#,SUM(QTY) GROUP BY S# HAVING SUM(QTY) > 500
s# sumofqty
------- -----------
1 2100
4 600
- 68 -
You can also specify a HAVING clause in your summary function by having
it return 1 or 0 when first == -1 depending upon whether you want to
include the row or not. For example, you could duplicate the above
query without the tselect statement by adding the following case to
calcsum
case -1 :
return ((group *)out)->sum > 500;
Set Group queries
Grouping functions, like "if" functions, provide querying possibilities
which go far beyond even the capabilities of even high level query
languages like SQL. One such type of query is one which groups rows
based upon sets of data. A set of data might be something like all red
parts. With a set related query you could then ask questions like
Which suppliers supply no red parts?
Which suppliers supply only red parts?
Which suppliers supply every red part?
Which suppliers supply exactly red parts?
Which suppliers supply no red parts?
int groupno(void *in, void *out, int first)
{
static int flag;
switch (first) {
case 1 :
*(int *)out = ((order *)in)->s; flag = 0;
case 0 :
if (tlookup(set, "p#", EQ, ((order *)in)->p, NULL))
flag = 1;
break;
case -1 :
return !flag;
}
}
set = tproject(STATIC, parts, "p#", "color", EQ, "red", NULL);
answer = tgroup(STATIC, orders, "s#", "s# i", groupno);
tdrop(set);
s#
-------
4
groupno requires that a set be created before the queried is executed.
You must remember to remove the set afterwards.
- 69 -
You may have noticed that the answer to the groupno query does not
contain supplier #5! Is there an error in groupno? Well, yes and
no. groupno does select only those suppliers which supply no red
parts, but only those which supply at least one part. Any suppliers
who do not supply any parts will not appear in the groupno query.
This is because the group set occurs on the orders table, not the sups
table. Fortunately, grouponly, groupevery, and groupexactly do not
suffer from this problem since they only work with suppliers who do
supply parts.
What suppliers supply only red parts?
int grouponly(void *in, void *out, int first)
{
static TABLE *gset;
int i;
switch (first) {
case 1 :
gset = tcreat(STATIC, "*p# i");
*(int *)out = ((order *)in)->s;
case 0 :
tload(gset, ((order *)in)->p);
break;
case -1 :
i = trows(tdiff(TEMP, gset, set)) == 0;
tdrop(gset);
return i;
}
return 1;
}
set = tproject(STATIC, parts, "p#", "color", EQ, "red", NULL);
answer = tgroup(STATIC, orders, "s#", "s# i", grouponly);
tdrop(set);
s#
-------
2
3
Like all the group set queries, grouponly requires that a set containing
all red parts be created before the actual query is performed.
grouponly may be the most complex query we have seen so far. It
requires that the group function itself generate a table, then perform
a set comparison. grouponly, groupevery and groupexactly are very
similar in design.
- 70 -
This is how it works. grouponly performs the query by creating a set
of all the parts ordered by suppliers in that particular group, gset.
grouponly then compares gset to set with the tdiff function. If the
difference of gset - set yields a table with 0 rows, that means that
gset contained only those parts found in set. Hence, every part
supplied by that supplier (gset) was a red part (set).
When grouponly is called with first equals 1, it creates the gset table.
It only needs to contain one column, p#, and it fact it must, since it
needs to be compatible with set so that they will work with tdiff.
out is also initalized at this point to the current group's supplier #.
Since gset is declared within grouponly it must be declared as static,
otherwise the table would disappear every time grouponly returned to
tgroup.
grouponly then loads gset with the part#'s ordered by that supplier.
When every row in the group has been processed, gset will contain
every part# ordered by that supplier. Notice that the p# column in
gset is declared as the primary key (as is set). This is not required.
grouponly will work fine is either or both are not keyed. However, on
larger tables, creating the sets with a primary key will most likely
improve performance.
Now the tricky part. grouponly now must compare the two sets. It does
this after the entire group has been processed, when first equals -1.
tdiff is used to compare gset to set. As explained above, if the
difference between the two yields a table with 0 rows, then grouponly
returns 1, meaning tgroup should include out in the answer table,
otherwise, grouponly returnes 0. Importantly, grouponly also drops
the gset table it created. If it didn't the table would lie around
in memory until the program terminated.
What suppliers supply every red part?
int groupevery(void *in, void *out, int first)
{
static TABLE *gset;
int i;
switch (first) {
case 1 :
gset = tcreat(STATIC, "*p# i");
*(int *)out = ((order *)in)->s;
case 0 :
tload(gset, ((order *)in)->p);
break;
case -1 :
i = trows(tdiff(TEMP, set, gset)) == 0;
tdrop(gset);
return i;
}
return 1;
}
- 71 -
set = tproject(STATIC, parts, "p#", "color", EQ, "red", NULL);
answer = tgroup(STATIC, orders, "s#", "s# i", groupevery);
tdrop(set);
s#
-------
1
2
groupevery is identical to grouponly except that in the tdiff statement,
gset and set are switched around. Since set contains every red part,
if the difference of set and gset is 0, then gset must also contain
every red part. gset can also contain parts that are not red, just as
long as it contains every one that is.
Which suppliers supply exactly red parts?
int groupexactly(void *in, void *out, int first)
{
static TABLE *gset;
int i;
switch (first) {
case 1 :
gset = tcreat(STATIC, "*p# i");
*(int *)out = ((order *)in)->s;
case 0 :
tload(gset, ((order *)in)->p);
break;
case -1 :
i = trows(tdiff(TEMP, gset, set)) == 0 &&
trows(tdiff(TEMP, set, gset)) == 0;
tdrop(gset);
return i;
}
return 1;
}
set = tproject(STATIC, parts, "p#", "color", EQ, "red", NULL);
answer = tgroup(STATIC, orders, "s#", "s# i", groupexactly);
tdrop(set);
s#
-------
2
- 72 -
groupexactly is a cross between grouponly and groupevery. Common
sense tells use this is true. For gset to exactly match set it must
contain every part in set, and only those parts in set. Thus
groupexactly tests for both. If either is false then the sets are
not exactly alike. Only one supplier, #2, supplies exactly red parts.
Notice the supplier #2 is the only supplier found in both the grouponly
result and the groupevery result.
Optimizing Queries
As queries become more and more complex, the variety of ways in which
they can be accomplished increases. But which way is best? By
following some general guidelines, you can help improve the performance
times of your queries. The following is a list of functions in the
order or level at which they should pear in a query. When performing
queries which combine two or more of these functions, the ones at the
bottom of the list should be performed before, or within, those at the
top of the list.
tsort
tunion
tjoin
tintersect
tdiff
tprojectif
tselectif
tproject
tselect
If you want the results of a query sorted, tsort should always be the
last or outermost function. It doesn't make any sense to nest tsort
within another query. Also, use temporary tables (TEMP and STATIC)
whenever possible. CRDE can often hold the intermediate results of a
query completely in memory, thus improving performance.
- 73 -
Chapter 12
Creating Reports
Single-table reports
In the last section we discussed how to use CRDE to generate queries
and ask questions about the data in your database. Often you will
want to keep the results of a query permanent, on paper. The CRDE
function usually used for generating reports is tscanif. tscanif
doesn't do anything accept call its if function for every row in the
table. If you tell the if function to print out those rows, you can
create a hard copy report of your tables and queries.
This simple example prints out a report on the sups table.
int line, page;
char *title;
#define LINESPERPAGE 54
int ReportSups1(void *rec)
{
/* print report header at top of page */
if (line == 0) {
fprintf(stdprn, "%*s\n", 40 + strlen(title), title);
fprintf(stdprn, "%41s %d\n\n", "PAGE", page);
fprintf(stdprn, " s# sname \n");
fprintf(stdprn, "------ " "-------------------------\n");
line += 5;
}
/* print a row */
fprintf(stdprn, "%6d %-25s\n", ((sup*)rec)->s,
((sup *)rec)->sname);
/* test for end of page */
if (line % LINESPERPAGE == 0) {
fprintf(stdprn, "\12");
line = 0;
}
return 1;
}
line = 0; page = 1;
title = "SUPPLIER REPORT";
tscanif(sups, ReportSups1, NULL);
- 74 -
The above ReportSups1 report is generic enough to be used in a variety
in situations, namely any query which generates a subset of the sups
table. For example, this portion of code uses the ReportSups1 to
generate a report listing all those suppliers with an above average
status.
double d;
taverage(sups, "status", &d);
answer = tsort(STATIC,
tselect(TEMP, sups, "status", GT, (int)d, NULL),
"sname"
);
line = 0; page = 1;
title = "SUPPLIERS WITH ABOVE AVERAGE STATUS";
tscanif(answer, ReportSups1, NULL);
The above report uses the same report function, ReportSups1, to output
a list of all suppliers with above average status, sorted by supplier
name. Of course, since tscanif is a search-related function, simple
queries can be performed by tscanif itself. For example to list only
those suppliers in Boston
line = 0; page = 1;
title = "SUPPLIERS IN BOSTON";
tscanif(sups, ReportSups1, "city, EQ, "Boston", NULL);
However, since most reports either are more complex, or require that
the report be sorted, you will generally find it more common to create
a separate query rather than filter a table through tscanif.
Multi-table reports
You can produce multi-table reports with CRDE just as easily as single-
table reports. There are two basic methods for mutli-table reports:
the join method, and the lookup method.
The join method
The join method requires that you create the multi-table report by
joining all the linked tables together into a single table, and then
produce the report. For example, suppose you wanted to create a
verbose orders report containing all orders with both supplier and
part information.
- 75 -
int line, page;
char *title;
#define LINESPERPAGE 54
int ReportOrders1(void *rec)
{
/* this typedef must exactly match joined table's structure */
typedef struct {
int s;
char sname[26];
char city[26];
int status;
int p;
char pname[16];
char color[11];
int qty;
} spo;
/* print report header at top of page */
if (line == 0) {
fprintf(stdprn, "%*s\n", 40 + strlen(title), title);
fprintf(stdprn, "%41s %d\n\n", "PAGE", page);
fprintf(stdprn, "s# sname "
" city status p# pname "
" color qty\n");
fprintf(stdprn, "------ ------------------------- "
"--------------- ------ --------------- ---------- "
"------\n");
line += 5;
}
/* print an order */
fprintf(stdprn, "%6d %-25s %-25s %6d %6d %-15s %-10s %6d\n",
((spo *)rec)->s,
((spo *)rec)->sname,
((spo *)rec)->city,
((spo *)rec)->status,
((spo *)rec)->p,
((spo *)rec)->pname,
((spo *)rec)->color,
((spo *)rec)->qty
);
/* if end of page, form feed */
if (line % LINESPERPAGE == 0) {
fprintf(stdprn, "\12");
line = 0;
}
return 1;
}
- 76 -
answer = tjoin(TEMP, sups, "s#",
tjoin(TEMP, part, "p#", orders, "p#","s#,p#,pname,color,qty"),
"s#",
"s#,sname,city,status,p#,pname,color, qty"
);
line = 0; page = 1;
title = "ORDERS REPORT";
tscanif(answer, ReportOrders1, NULL);
The lookup method
The lookup method takes a different approach. It scans each row in
the orders table, then uses the tget function to retrieve supplier
and part information.
int line, page;
char *title;
int ReportOrders2(void *rec)
{
sup srec;
part prec;
/* print report header at top of page */
if (line == 0) {
fprintf(stdprn, "%*s\n", 40 + strlen(title), title);
fprintf(stdprn, "%41s %d\n\n", "PAGE", page);
fprintf(stdprn, "s# sname "
" city status p# pname "
" color qty\n");
fprintf(stdprn, "------ ------------------------- "
"--------------- ------ --------------- ---------- "
"------\n");
line += 5;
}
/* look up supplier */
i = tget(sups, &srec, 1, "s#", EQ, ((order *)rec)->s, NULL);
if (i < 0) return i;
/* look up part */
i = tget(parts, &prec, 1, "s#", EQ, ((order *)rec)->p, NULL);
if (i < 0) return i;
/* print an order */
fprintf(stdprn, "%6d %-25s %-25s %6d %6d %-15s %-10s %6d\n",
((sup *)srec)->s,
((sup *)srec)->sname,
((sup *)srec)->city,
((sup *)srec)->status,
((part *)prec)->p,
((part *)prec)->pname,
((part *)prec)->color,
((order *)rec)->qty
);
- 77 -
/* if end of page, form feed */
if (line % LINESPERPAGE == 0) {
fprintf(stdprn, "\12");
line = 0;
}
return 1;
}
line = 0; page = 1;
title = "ORDERS REPORT";
tscanif(answer, ReportOrders1, NULL);
The method presented with tscanif is not the only possibility for
creating reports, although it is probably the most common. tget could
be used to place rows in an array where they could be manipulated
freely. tgroup is also a possibility for reporting, using its
automatic grouping abilities to output reports based upon groups
and subtotals. In reality, the possibilities are endless. CRDE's
intimate relationship with the C language gives it the flexibility to
create even the most complex reports.
- 78 -
Chapter 13
Maintaining Database Integrity
The term "database integrity" refers to the accuracy or correctness
of data in the database. Making sure the data in a database is correct
is a very important job. Many data managers, including CRDE, provide
assistance to support database integrity. Integrity in relational
databases can be broken down into three basic categories: entity
integrity, domain integrity, and referential integrity.
Entity integrity
Entity integrity refers to the fact that the key values in a
relational table should be unique in that table. This sort of
integrity is handled automatically by CRDE whenever you define a
primary key on a table. No matter what operations you perform upon
a keyed table, CRDE always guarantees the uniqueness of its key values
within the table. CRDE allows you to get around the entity integrity
rule by creating non-keyed tables. Non-keyed tables can be useful in
a number of situations.
Domain integrity
Domain integrity refers to the correctness of data in a particular
column. Every column has a practical set of values, a domain, which
it should hold. For example, the domain for the "age" column is
probably an integer between 1 and 99, the domain for the "sex" column
is either "Male" or "Female", etc. A data management system which
supports domain integrity would not allow you to enter 800 into the
"age" column, for example.
CRDE does not support domain integrity directly, i.e. there are no CRDE
functions which you can call to set the domain of a particular column.
However domain integrity is relatively simple to implement using C.
For example, you might write a function which would screen the input
of suppliers so that the "status" column contained a value between 0
and 99.
int InsertSup(sup *s)
{
if (s->status < 1 || s->status > 99)
return 0;
return tinsert(sups, s, 1);
}
- 79 -
InsertSup returns 1 if the row was inserted, or 0 if it was not. You
might want to improve InsertSup by insuring that all text columns in
the row are set to upper case.
int InsertSup(sup *s)
{
if (s->status < 1 || s->status > 99)
return 0;
strupr(s->sname);
strupr(s->city);
return tinsert(sups, s, 1);
}
Referential integrity
Referential integrity refers to the existence of references between
tables which are linked by key column(s). For example, the orders
table has two columns, s# and p#, which are used as references to the
sups and parts tables respectively. If there existed an order in the
orders table like
s# p# qty
------- ------- -------
1 1 100
there must also exists a supplier #1 in the sups table and a part #1
in the parts table otherwise the order makes no sense.
Although CRDE does not support referential integrity directly, its
high level functions make the task almost trivial.
Foreign keys and primary keys
The concept of a foreign key is the basis for referential integrity.
As we know, a primary key consists of columns in a table which may not
contain duplicate values. A foreign key is/are column(s) in another
table which reference the table's primary key. For example, the "s#"
column in the orders table is a foreign key to the sups table because
it references the "s#" column in sups. Likewise, the "p#" column is a
foreign key to the parts table. For refential integrity to remain
intact, there must always exists a primary key for each foreign key in
a database.
Referential integrity and inserting
When you insert rows into a table, you want to be sure that any foreign
key columns have references. This is easily handled with CRDE's tlookup
function. The following function inserts attempts to insert a row into
the orders table with full referential checking. Lookups are made to
insure that the order's supplier # and part # are valid.
- 80 -
int InsertOrder(order *o)
{
int i;
i = tlookup(sups, "s#", EQ, o->s, NULL);
if (i > 0) {
i = tlookup(parts, "p#", EQ, o->p, NULL);
if (i > 0)
return tinsert(orders, &o, 1);
}
return i;
}
InsertOrder uses the tlookup function to insure that referential
integrity will remain intact when the order is inserted. InsertOrder
contains full error-checking, and is a good model of how to use CRDE
functions in real applications. InsertOrder returns 1 if successful,
or 0 if referential integrity would be comprimised. If an error
occurs (like a disk seek error), InsertOrder returns the correct error
code of the offending error.
Referential integrity and deleting
There is a similar problem when deleting rows from tables. Care must
be taken so that a reference for some table is not accidentally deleted.
If you want to delete a supplier from the sups table, you must be sure
that referential integrity is maintained with the orders table. The
following demonstrates four different methods to implement referential
integrity when deleting a supplier.
int DeleteSup1(int supno)
{
int i;
i = tlookup(orders, "s#", EQ, supno, NULL);
if (i == 0) {
return tdelete(sups, "s#", EQ, supno, NULL);
}
return i < 0 ? i : 0;
}
DeleteSup1 is similar to the SQL command ON DELETE RESTRICT. It
restricts you from deleting a supplier if any orders reference it.
DeleteSup1 returns > 0 if successful, 0 if deletion was restricted,
or < 0 if an error occurred.
int DeleteSup2(int supno)
{
int i;
i = tdelete(orders, "s#", EQ, supno, NULL);
if (i < 0)
return i;
return tdelete(sups, "s#", EQ, supno, NULL);
}
- 81 -
DeleteSup2 is similar to the SQL notation ON DELETE CASCADE. In this
case, if a supplier is deleted, all its orders are deleted as well.
DeleteSup2 follows the same return value scheme as DeleteSup1.
int DeleteSup3(int supno)
{
int i;
i = tdelete(orders, "s#", -1, NULL, "s#", EQ, supno, NULL);
if (i < 0)
return i;
return tdelete(sups, "s#", EQ, supno, NULL);
}
DeleteSup3 is similar to the SQL notation ON DELETE SET NULL. If a
supplier is deleted, the "s#" reference in all its orders are set to
a special value, -1, marking them as unreferenced orders. The program
might use this information to print a report containing all those orders
made by suppliers since deleted. DeleteSup3 follows the same return
value scheme as DeleteSup1.
TABLE *deleted;
deleted = tborrow("deleted", orders);
int DeleteSup4(int supno)
{
int i;
i = tadd(deleted, tselect(TEMP, orders, "s#", EQ, supno, NULL));
if (i < 0)
return i;
i = tdelete(orders, "s#", EQ, supno, NULL);
if (i < 0)
return i;
return tdelete(sups, "s#", EQ, supno, NULL);
}
DeletSup4 is similar to DeleteSup3 except that any deleted orders
are first saved in a special deleted table, so that they may be
referenced later. deleted is created with the identical structure
or orders, using the tborrow function. DeleteSup4 follows the same
return value scheme as DeleteSup1.
Notice that because referential integrity is not built in, CRDE gives
you the ability to implement referential integrity in a variety of ways,
not just limited to the ones shown above.
Referential integrity and changing.
Problems with referential integrity can occur when referenced values
are changed in a table. This can happen, for example, if a supplier's #
is changed and the orders table is not kept in sync.
- 82 -
int ChangeSup1(int oldno, int newno)
{
int i;
i = tchange(sups, "s#", newno, NULL, "s#", EQ, oldno, NULL);
if (i > 0) {
i = tchange(orders, "s#", newno, "s#", EQ, oldno, NULL);
if (i < 0)
return i;
return 1;
}
return i < 0 ? i : 0;
}
Changing the supplier # however introduces a new variable: you may
accidently try to change the "s#" to a value which already exists.
What will tchange do in this case? If changing a row would violate
the primary key of a table, tchange will not change the row and returns
0, for 0 rows changed. If tchange can change the row, then it will
return 1, indicating that the "s#" was changed.
ChangeSup1 returns 1 if the supplier # was changed, 0 if it could
not because the new supplier # already existed, or an error code (< 0)
if an error occurred.
- 83 -
Chapter 14
Performing Transactions
CRDE's transaction tracking features
CRDE provides complete transaction tracking facilities. A transaction
is a series of changes made to a table. CRDE allows you to track
transactions on a table by table basis.
int ttransact(TABLE *t);
int trollback(TABLE *t);
int tcommit(TABLE *t);
int tmark(TABLE *t);
The ttransact function begins a transaction on a table. Once ttransact
is called, all changes made to the table are pending until you decide
to keep or throw out the changes. A call to trollback returns that
table back to its state prior to beginning the transaction. tcommit
makes any changes during the transaction permanent. In either case,
the transaction is considered over. To start another transaction, you
must call the ttransact function again. You can use the tmark to
determine whether a table is in a transaction or not. tmark returns 1
if a table is in a transaction, and 0 if it is not. If you close a table
which is in the middle of a transaction, the transaction is automatically
committed before the table is closed. Similarly, if you drop a table
during a transaction, the transaction is automatically rolled back before
the table is dropped.
CRDE places few restrictions on what you can do with tables during a
transaction. You can query and update them like any other table. The
only limitation made by CRDE is that you may not create or drop secondary
indexes. Attempting to do so will result in an error.
The following example demonstrates CRDE's transaction tracking
capabilities at work.
include <conio,h>
include <stdio.h>
include "crde.h"
int tbuffers = 256; /* set as large as possible when doing
transaction tracking */
int ShowTable(TABLE *t)
{
clrscr();
gotoxy(1, 1); cputs("Displaying a table.");
gotoxy(1, 2); cprintf("%ld rows found.", trows(t));
return tview(t, NULL, NULL, 3, 3, 78, 24, 0x07, 0x07, 0x07, NULL);
}
- 84 -
int main()
{
TABLE *t;
/* open and display table */
t = topen("sups");
ShowTable(t);
/* begin transaction */
ttransact(t);
/* perform operation(s) on table */
tdelete(t, "city", EQ, "Boston", NULL);
ShowTable(t);
/* restore original table with single function call! */
trollback(t);
ShowTable(t);
tclose(t);
}
How does transaction tracking work?
To be able to rollback a table in a transaction, CRDE must record all
the changes you make to the table while the transaction is in effect.
CRDE keeps track of these changes in memory. This has several advantages,
the greatest of which is speed. This allows you to use transaction
tracking with virtually no performance degradation. The significant
drawback of this is that you are limited to the number of changes you
can make by the amount of available memory in your computer. If the
tables you are performing transactions on are large and/or numerous,
you may run out of memory. When this happens, CRDE functions will start
returning -11 "out of memory" error. If memory runs out you should
immediately rollback any transactions before continuing. The amount of
transactions which can be performed depends upon many factors: the
number and size of table in the transaction, the number of changes
made, and available memory. If the tables are small enough you will
be perform an unlimited amount of transactions. To perform an
unlimited amount of transactions on a table, there should be at least
size of table (on disk) + size of indexes (on disk)
bytes of memory set aside in your buffers (see Chapter 2 about setting
the tbuffers system variable). This is a rough estimate which does not
take into account other factors which could possibly make the requirements
steeper. For optimum performance, you should consider setting the
number of buffers to double that amount.
Even large CRDE applications are no likely to be larger than 200-250k
(with CRDE linked). On a 640k machine that would leave some 300-400k
available to CRDE. With that much available memory, you should have
no problem performing unlimited transactions on a tables with up to
5000 rows or more.
- 85 -
Chapter 15
Repairing Tables
Corrupted indexes
The most common type of corruption is when an index becomes corrupted.
This can happen when CRDE is updating the indexes on a table and
something unexpected happens. Index corruption will usually show itself
as a incorrect query result or a "disk seek error", meaning that pointers
are out of alignment in the index file. If you can open a table with
topen, and you're having problems, this is probably what is wrong.
You may also have trouble viewing the table if its index(es) are corrupt.
Testing your indexes
There is an easy way to test the integrity of an index visually by
using the tview function. Simply call tview on the table using the
same sort order as the index in question. For example, suppose you
wanted to test the integrity of an index on the "city" column (if one
existed) in sups. First you would set up tview to view the table like
tview(sups, NULL, "city", 3, 3, 78, 23, 0x07, 0x07, 0x07, NULL);
Scroll throughout the entire table. If tview aborts prematurely, the
cursor gets stuck, or negative numbers appear in the row number column,
the index probably corrupt. You can rebuild any suspect indexes with
the tindex function. Many times, however, more than one index is
corrupt. An easier way to rebuild your indexes is to go to DOS, delete
them, and then open the table with topen. topen will automatically
rebuild all of the missing indexes for you. This is convenient when
you may not remember all of the indexes on a table.
Repairing a table with trepair
Sometimes, however, the problem is much deeper than a corrupt index.
For example, you may not even be able to open the table with topen,
or when you do, your whole program blows up. If topen refuses to
open the table, your only alternative is to attempt to repair the table
itself. Fortunately, CRDE provides such a repair facility. trepair
is a CRDE function declared in crde.h as
TABLE *trepair(char *name, char *td);
- 86 -
name is the name of the table to be repaired. td is a table descriptor
which must exactly describe the structure of the original table.
trepair uses this model to rebuild the table. trepair will attempt to
recover as much of the table and primary index as it possibly can.
trepair does not repair secondary indexes. However, once the table is
repaired, you can simply rebuild them with the tindex function.
Limitations of trepair
trepair can repair most logically damaged table without a hitch.
Logically damaged tables are those which contain incorrect or outdated
information. Usually this type of corruption occurs when all unwritten
information was not committed to disk, such as during a power-outage
or similar catastrophe. trepair can even fix certain types of
physically damaged tables. Physically damaged tables are those which
have defective sectors or some other type of physical damage (the table
was partially overwritten or truncated). However, if your table is
physically damaged, your problems may be deeper than just a corrupted
table. Your disk may need to be reformatted or your disk drive repaired.
trepair however, is fairly limited in the amount of physical damage it
can repair. trepair cannot unerase tables nor fix bad sectors or
clusters in a file. In addition, trepair must be able to open a table
to repair it.
- 87 -
Reference Manual
This chapter contains a detailed description of all the functions in
the CRDE library.
The following sample library look-up entry explains how to use this
portion of the Referance Manual to reference CRDE library functions.
Name The function's name.
Description Simple explanation of what the function does.
Declaration Function's full standard C declaration.
Remarks A detailed explantion of how a function works, how to
use it, what its used for, and any other details
relating to the function.
Return Value Possible return values for the function.
See Also Related functions/topics.
Example An example demonstrating how to use the function.
Output Output of the example program, if any.
Standard conventions in CRDE
The CRDE function library was designed to be consistant in the way
that functions return values and accept parameters. Below are some
basic rules common to all CRDE functions.
Return values
Every CRDE function returns some type of error indicator in the event
of an error. The type of indicator depends upon the return type of
the function.
- 88 -
Return type Possible return values
TABLE * Returns a pointer to an open table if successful, or
NULL if an error occurs.
int Returns 0 if successful, or a negative error code if
an error occurs.
long Returns the number of rows involved in the operation,
or a negative error code if an error occurs.
char Returns 0 on error.
Note: All of CRDE's error codes are negative integers. In addition,
CRDE sets the global variable terrno to the appropriate error code
whenever an error occurs. If the function does not return an integer
type, you can use terrno to determine the cause of the error. If the
function does return an integer type then terrno will match the error
code returned by the function. CRDE also has another global variable
called trowsfound. trowsfound contains the number of rows (if any)
involved in the last operation. Like terrno, trowsfound can be used to
determine the number of rows affected by the last operation if the
function does not return an integer value. In the event of an error,
trowsfound will hold the number of rows affected before the error occured.
Functions which return a TABLE *
Every CRDE function which returns a TABLE * accepts a char *name as
its first parameter. name may be any of three values:
TEMP The table is a TEMP table.
STATIC The table is a STATIC table.
If a table name is not TEMP or STATIC, it is assumed to be an asciiz
string containing the table's base name. The string maybe any legal
DOS pathname up to 64 characters in length. It may not include
wildcards or an extension. Any table previously existing by that name
is automatically overwritten by the new table. If a table is TEMP or
STATIC, it is known as a temporary table. Otherwise the table is a
permanent table.
Some functions return a table whose structure is identical to the one
passed to it as a parameter. Some examples are tcopy, tborrow, and
tselect. What this means is that the resulting table will have the
same column names, types, and primary key as the original table.
Secondary indexes, however, are not considered as part of a table's
structure and are never inherited by the result table.
Several functions which accept two tables as parameters require that
both tables be compatible. Examples are tunion, tdiff, and tintersect.
Two tables are compatible if they have identical column types and sizes
on a column by column basis. They may have different column names, and/
or a different primary key and secondary keys.
- 89 -
In some functions, whether or not a table has a primary key can have
an affect on how the function works. Such variations are noted in the
reference guide.
Parameters in CRDE functions
When at all possible (in the Reference Manual and in crde.h), function
prototypes use symbolic parameter names to help identify the meaning of
the parameter. Those parameters and their meanings are listed below.
Paramter name Meaning
name The name parameter is the first parameter of any
CRDE function returning a TABLE *. name may be
TEMP, STATIC, or a pointer to a legal DOS
pathname in asciiz format. The pathname may not
contain wildcards or an extension.
t, t1, t2, etc. These parameters are tables passed to the function.
c This parameter expects a pointer to an asciiz string
containing a column name like "cust#".
cl This parameter expects a pointer to a asciiz string
containing a list of column names seperated by
whitespace.
td This parameter expects a table descriptor which is
very similar to the column list above. A table
descriptor also contains data type information. Any
column preceded with an asterisk '*' are part of the
table's primary key.
id This parameter expects an index descriptor similar to
the column list above. However, any columns
preceded with and exclaimation mark '!' are indexed in
descending order.
pl This parameter expects a special type of column list.
Columns in the list may be preceded with an asterisk
'*', marking them as part of a primary key. It is
different from a table descriptor because no data type
information is required.
Auto-cleanup of TEMP tables
When a TEMP table is passed to a CRDE function, it may be automatically
dropped when the function completes. This feature allows you to nest
functions which return tables inside one another safely. The general
rule is:
- 90 -
A CRDE function will automatically drop a TEMP table
passed to it as a parameter if the function does not or can not
alter the table in any way. If the TEMP table cannot be thus
affected by the function, it will be automatically dropped when
the function completes, even in the event of an error.
Some CRDE functions will not automatically drop a table, regardless of
its type. These functions are: ttransact, tcommit, trollback, tmark,
twritec, tnormal, tmode.
- 91 -
----------------------------------------------------------------
day
----------------------------------------------------------------
Description Determines calender day of a date_t value.
Declaration int day(date_t d);
Remarks day returns a value between 1 and 31.
Return Value day returns the calender day of the specified date,
or -1 if the date is invalid.
See Also month, year
Example #include "date.h"
int printdate(date_t d);
{
printf("%d/%d/%d", month(d), day(d), year(d));
}
----------------------------------------------------------------
dayofweek
----------------------------------------------------------------
Description Determines the day of week of a particular date.
Declaration int dayofweek(date_t d);
Remarks dayofweek returns a value from 0 to 6 correponding the
day of week, Sunday to Saturday, of the specified date.
date.h provides 7 constants analogous to the return
value of the dayofweek function.
#define SUNDAY 0
#define MONDAY 1
etc...
#define SATURDAY 6
Return Value dayofweek returns a value from 0 to 6, corresponding
to the day of week, or -1 if the date is invalid.
See Also firstday.
Example #include "date.h"
/* functions to determine whether a date is on a weekend
or weekday */
- 92 -
int weekend(date_t d)
{
int i;
i = dayofweek(d);
return i == SATURDAY || i == SUNDAY;
}
int weekday(date_t d)
{
return !weekend(d);
}
----------------------------------------------------------------
dayofyear
----------------------------------------------------------------
Description Determines the day of year of some calender date.
Declaration int dayofyear(int month, int day, int year);
Remarks dayofyear returns a value between 1 and 366 where
January 1 is day 1, January 2 is day 2, February 1 is
day 32, etc.
Return Value Returns the day of year, or -1 if the date is invalid.
See Also monthday.
Example #include <stdio.h>
#include "date.h"
int main()
{
char buf[16];
int m, d, y;
printf("Enter date: ");
scanf(" %d / %d / %d", &m, &d, &y);
printf("Days since Jan 1 is %d.\n", dayofyear(m, d, y));
}
Output Enter date: 2/1/1990
Days since Jan 1 is 32.
- 93 -
----------------------------------------------------------------
daysinyear
----------------------------------------------------------------
Description Returns number of days in a year.
Declaration int daysinyear(int year);
Remarks daysinyear returns value of 365 or 366 depending upon
whether the year is a leap year or not.
Return Value daysinyear returns the number of days in a year.
See Also leapyear.
Example #include <stdio.h>
#include "date.h"
int main()
{
printf("Days in the year 1990 = %d.\n",
daysinyear(1990));
printf("Days in the year 2000 = %d.\n",
daysinyear(2000));
}
Output Days in the year 1990 = 365.
Days in the year 2000 = 366.
----------------------------------------------------------------
firstday
----------------------------------------------------------------
Description Returns date of first day of week in a particular month.
Declaration date_t firstday(int month, int year, int dayofweek);
Remarks firstday returns a date, as a date_t value, for the
first dayofweek in that month and year. For example,
to get the date of the first Monday of August 1990
would look like
date_t firstmon;
firstmon = firstday(6, 1990, MONDAY);
MONDAY is a constant defined in date.h specifying the
day of the week.
- 94 -
firstday can also be used to calculate the second,
third, and fourth occurances of a date by simply adding
the appropriate multiple of 7 to the result. The
second Monday in June 1990 would be
firstday(6, 1990, MON) + 7;
Return Value firstday returns a date_t value or -1 if the date is
invalid.
See Also dayofweek.
Example /* function to determine if today is a payday (the
second and fourth Fridays of every month). */
int PayDay(date_t today)
{
int m, y;
date_t d;
m = month(today); y = year(today);
d = firstday(m, y, FRIDAY);
return (today == d+7) || (today == d+21);
}
----------------------------------------------------------------
gmdate
----------------------------------------------------------------
Description Converts a date_t value into a calender date.
Declaration int gmdate(date_t d, int *month, int *day, int *year);
Remarks gmdate converts the date_t value d into its calender
definition, and stores the results in month, day, and
year.
Return Value gmdate returns 0 if successful, or -1 if the date was
invalid.
See Also mkdate.
Example /* function to return the first day of the month of the
current date */
date_t FirstOfMonth(date_t dt)
{
int m, d, y;
gmdate(dt, &m, &d, &y);
return mkdate(m, 1, y);
}
- 95 -
----------------------------------------------------------------
leapyear
----------------------------------------------------------------
Description Determines if a year is a leap year.
Declaration int leapyear(int year);
Remarks leapyear recognizes both leap years and leap centuries.
A year is a leap year is it is evenly divisible by 4,
excluding those centuries (1800, 1900, 2100, etc.) not
evenly divisible by 400.
Return Value leapyear returns 1 year is a leap year, or 0 if it is not.
See Also daysinyear.
Example #include <stdio.h>
#include "date.h"
int main()
{
int i;
printf("Enter year: ");
scanf(" %d", &i);
if (leapyear(i))
printf("%d is a leap year.\n", i);
else
printf(%d is not a leap year.\n", i);
}
----------------------------------------------------------------
mkdate
----------------------------------------------------------------
Description Converts a calender date to a date_t value.
Declaration date_t mkdate(int month, int day, int year);
Remarks mkdate converts the calender date month, day, year
into its date_t equivelent. mkdate knows about the
days in months and years, and about leap years, and
will return -1 if the date is invalid, for example
2/29/1990.
mkdate can convert dates from 1/1/1 to 12/31/32767.
A common error when using mkdate is to forget to
indicate the correct century in the year, for exmaple
mkdate(4, 17, 90)
specifies the date 4/17/0090 not 4/17/1990.
- 96 -
Return Value mkdate returns a date_t value or -1 if the date is
invalid.
See Also gmdate.
Example #include <stdio.h>
#include "date.h"
int main()
{
date_t today;
int m, d, y;
do {
printf("Enter today's date: ");
scanf(" %d / %d / %d", &m, &d, &y);
today = mkdate(m, d, y);
} while (today < 0);
gmdate(today + 7, &m, &d, &y);
printf("Date a week from now = %d/%d/%d.\n", m, d, y);
}
----------------------------------------------------------------
month
----------------------------------------------------------------
Description Returns calender month of a date_t value.
Declaration int month(date_t d);
Remarks month returns a value between 1 and 12.
Return Value month returns the month of the date or -1 if the
date is invalid.
See Also day, year.
Example See day.
----------------------------------------------------------------
monthadd
----------------------------------------------------------------
Description Performs month addition/subtraction on a date.
Declaration date_t monthadd(date_t d, int months);
- 97 -
Remarks monthadd adds the specified number of months to a date
creating a new date. monthadd knows about calender
dates and leap years, and correctly adds the number of
months specified. For example
monthadd(mkdate(2, 1, 1992), 1);
correctly yields 3/1/1992.
You can subtract months from a date by simply specifying
a negative value for months.
Return Value monthadd returns a date_t value, or -1 if the date is
invalid.
See Also yearadd.
Example #include "date.h"
/* function to determine if an payment was received
within a month of its due date. If not, a surcharge
will be added to the amount owed */
int ReturnBy(date_t due, date_t received)
{
return received <= monthadd(due, 1);
}
----------------------------------------------------------------
taccess
----------------------------------------------------------------
Description Determines the accessibility of a table.
Declaration int taccess(char *name, int mode);
Remarks taccess tests a file for certain accessibility rights.
mode determines the type of test which will be done.
When mode is 0 taccess tests for existance of a file.
For example, to see if the "sups" table had been created
TABLE *sups;
if (taccess("sups", 0) == 0)
/* file exists so open it */
sups = topen("sups");
else
/* file does not exist, create new */
sups = tcreat("sups", "*s# i, sname c26, city"
"c26, status i");
taccess returns 0 if the tables exists. You can test
for write permission to a table by setting mode to 2.
- 98 -
Return Value taccess returns 0 if access rights tested positive, or
-1 if not.
----------------------------------------------------------------
tadd
----------------------------------------------------------------
Description Inserts records from one table into another.
Declaration long tadd(TABLE *t1, TABLE *t2);
Remarks tadd inserts the records in t2 into t1. If t1 is
keyed, only those records which do not violate the
primary key will be inserted.
If t2 is a TEMP table, t2 is automatically dropped
by tadd, even if an error occurs. Otherwise, t2 is
unaffected by tadd.
Return Value tadd returns the number of records added if successful,
or an error code if an error occured.
See Also tupdate, tsubtract.
Example /* function to combine orders made during a month to a
history file containing all previous orders. Orders
table and History table are assumed to have compatible
structures. */
int SaveOrders(TABLE *orders, TABLE *history)
{
long i;
i = tadd(history, orders);
if (i > 0)
/* if orders successfully added then clear
orders table for next month */
tempty(orders);
return i > 0 ? 0 : i;
}
----------------------------------------------------------------
tall
----------------------------------------------------------------
Description Performs a (user-defined) function on all open tables.
Declaration int tall(int (*func)(TABLE *));
- 99 -
Remarks tall performs a function, accepting a TABLE * as a
parameter, returning an int, on all currently open
tables (temporary and permanent). CRDE keeps an
active list of all open tables during the course of
a program's execution. tall simply traverses this
list, performing func on each table. tall is designed
to work even with functions which alter the table list
such as topen and tclose. tall will only perform func
on every table which was open immediately prior to
calling tall. This allows tall to work with functions
like tclose, and also prevents it from working
recursively on functions which open tables.
tall will work directly with many CRDE functions such
as: tclose, tdrop, tempty, ttransact, trollback,
tcommit, twritec, tnormal, tflush, and trelease.
Return Value tall returns the first non-zero result of func,
otherwise it returns 0.
Example #1 #include <stdio.h>
#include "crde.h"
int tbuffers = 128;
int main()
{
TABLE *sups, *parts, *orders;
/* open some tables */
sups = topen("sups");
parts = topen("parts");
orders = topen("orders");
/* put all in writec mode */
tall(twritec);
/*
rest of user-program here...
*/
/* closes all tables */
tall(tclose);
}
Example #2 #include <stdio.h>
#include "crde.h"
/* function which closes all temporary tables */
- 100 -
int tdroptemp(TABLE *t);
{
int i;
i = tmode(t);
return (i == T_TEMP || i == T_STATIC) ? tdrop(t) : 0;
}
int tdroptempall()
{
return tall(tdroptemp);
}
----------------------------------------------------------------
taverage
----------------------------------------------------------------
Description Calculates the average of a column.
Declaration int taverage(TABLE *t, char *c, void *result);
Remarks taverage calculates the average of a column in a
table and places the result in the buffer pointed
to by result. c must be acolumn in the table.
taverage only works with types i, l, f, and $. The
type of buffer pointed to by result is always double
for taverage, regardless of the column type.
The value in result is undefined if an error occurs.
If t is a TEMP table, it will automatically be dropped
by taverage, even if an error occurs. Otherwise, t is
unaffected by taverage.
Return Value taverage returns 0 in successful, or an error code if
an error occured.
See Also tcount, tmax, tmin, tsum.
Example #include <stdio.h>
#include "crde.h"
int tbuffers = 128;
int main()
{
TABLE *sups, *answer;
double avg;
sups = topen("sups");
- 101 -
/* query all suppliers with above average status */
taverage(sups, "status", &avg);
answer = tselect(TEMP, sups, "status", GT, (int)avg,
NULL);
tview(answer, NULL, NULL, 3,3, 78, 23, 0x07, 0x07,
0x07, NULL);
tall(tclose);
}
----------------------------------------------------------------
tborrow
----------------------------------------------------------------
Description Creates a new (empty) table like another table.
Declaration TABLE *tborrow(char *name, TABLE *t);
Remarks tborrow creates a new (empty) table with the exact
same structure t. The result table will have same
columns and primary key as t. However, any secondary
keys are not copied by tborrow.
If t is a TEMP table, it is automatically dropped by
tborrow, even if an error occurs. Otherwise, t is
unaffected by tborrow.
Return Value tborrow returns a pointer to an open table if
successful, or NULL if an error occured.
See Also tcopy.
Example #include <stdio.h>
#include "crde.h"
int tbuffers = 128;
int main()
{
TABLE *sups, *saved;
/* program to remove some suppliers from the Sups
table and place them in a special achive table named
Saved */
sups = topen("sups");
saved = tborrow("saved", sups);
tadd(saved, tselect(TEMP, sups, "status", LT, 5, NULL);
tdelete(sups, "status", LT, 5, NULL);
- 102 -
tadd(saved, tselect(TEMP, sups, "status", GT, 25, NULL);
tdelete(sups, "status", GT 25, NULL);
tclose(sups);
tclose(saved);
}
----------------------------------------------------------------
tchange
----------------------------------------------------------------
Description Changes rows in a table.
Declaration long tchange(TABLE *t, ...);
Remarks tchange accepts two NULL terminated argument lists,
the first, a list of the changes to be made, followed
by a search expression to determine which of the
records to change.
A change list consists of zero or more changes. A
change consists of a column name followed by a value.
A typical tchange call will have the format:
tchange(t, chcol1, chval1, chcol2, chval2, ..., NULL,
colname1, relop1, value1, ..., NULL);
chcol[n] can be any valid column name of t.
chval[n] may be either a constant or a variable. The
type of chval[n] must be exactly the type of chcol[n].
The value of chcol[n] is changed to chval[n] in every
record selected.
The search expression is identical to the one described
in tselect. Only records which match the search
expression are changed.
Here are some examples:
/* set all quantities in a orders table to 0 */
tchange(orders, "qty", 0, NULL, NULL);
/* change credit of a single customer */
int newquant, custno;
...
tchange(cust, "qty", newquant, NULL, "cust#", EQ, custno,
NULL);
tchange accepts a maximum of 16 changes. It is not an
error to specify no changes, but doing so will have no
effect on the table.
- 103 -
Return Value tchange returns the number of rows changed if
successful, or an error code if an error occurred.
See Also tchangeif, tselect.
Example /* routine to change a supplier id # while maintaining
referential integrity */
int ChangeSup(int oldsup, int newsup)
{
int i;
i = tchange(sups, "s#", newsup, NULL, "s#", EQ,
oldsup, NULL);
if (i > 0)
i = tchange(orders, "s#", newsup, NULL, "s#", EQ,
oldsup, NULL);
return i;
}
----------------------------------------------------------------
tchangeif
----------------------------------------------------------------
Description Changes rows in a table.
Declaration long tchangeif(TABLE *t, int (*action)(void *), ...);
Remarks tchangeif is exactly like tchange except it accepts an
additional parameter action. Every row which matches
the search expression is passed to action for further
evaluation. action should behave exactly as the action
described in tselectif:
Result of action What happens
> 0 Row is changed.
= 0 Row is not changed.
< 0 An error occurred. tchangeif
is aborted and returns the
result of action.
See tselectif for more details on action.
tchangeif differs from the other "..if functions" in
one small detail. Any changes to the row passed to
action are made permanent along with any changes
listed in the change list (if, of course, action
returns an integer > 0.) Note: any changes listed
in the change list are made after the call to action,
and so will override any changes made inside of action
itself.
- 104 -
For example to give everyone in an employee table a 5%
salary increase:
TABLE *emps;
int increase(void *rec)
{
/* user-defined if function - note that it is
necessary to cast void * rec. */
((emp *)rec)->salary = ((emp *)rec)->salary * 0.05;
return 1;
}
...
tchangeif(emps, increase, NULL, NULL);
Return Value tchangeif returns the number of records changed if
successful, or an error code if an error occurred.
See Also tchange, tselect, tselectif.
----------------------------------------------------------------
tchcol
----------------------------------------------------------------
Description Renames a column.
Declaration int tchcol(TABLE *t, char *old, char *new);
Remarks tchcol renames column old as new. old must be a valid
column name in t, and new must be an unique to t. The
position, datatype, and primary key of the column
remain the same.
A transaction keeps track of any columns which are
renamed, so rolling back a transaction will cause any
columns which were renamed during the transaction to
be reversed.
You may not use tchcol on a table which is in read-
only mode. See tselectif for details.
Return Value tchcol returns 0 if successful, or an error code if an
error occurred.
Example #include "crde.h"
int tbuffers = 128;
- 105 -
int main()
{
TABLE *t;
/* program to change the sups column "sname" to "name" */
t = topen("sups");
tchcol(t, "sname", "name");
tclose(t);
}
----------------------------------------------------------------
tclose
----------------------------------------------------------------
Description Closes a table.
Declaration int tclose(TABLE *t);
Remarks tclose closes a table and all its associated indexes.
Unless the table is a temporary table, all unwritten
buffers are flushed to disk before closing. Likewise,
any transaction pending on the table is automatically
commited before closing. All memory held by table is
released upon closing. If the table is a temporary table
(TEMP or STATIC) it is deleted upon closing. There is
no difference between closing and dropping a temporary
table.
A program should always close all tables before
completing. A simple way to insure this is to issue a
tall(tclose);
at the end of a program.
tclose closes a table even if an error occurs.
Return Value tclose returns 0 on success, or an error code if an error
occured.
See Also tdrop, topen.
Example #include "crde.h"
int tbuffers = 128;
int main()
{
TABLE *sups, *parts, *orders;
sups = topen("sups");
parts = topen("parts");
orders = topen("orders");
- 106 -
/* main part of program here... */
tclose(sups);
tclose(parts);
tclose(orders);
}
----------------------------------------------------------------
tcmpstruct
----------------------------------------------------------------
Description Compares two table's structures.
Declaration int tcmpstruct(TABLE *t1, TABLE *t2);
Remarks tcmpstruct compares the tables on a column by column
basis, for identical types and sizes and primary key.
Column names are not considered. tcmpstruct returns
one of the following:
Return value Meaning
0 Tables are different.
1 Tables have same structures but
different primary keys.
2 Tables are identical.
Whether or not either table has any secondary keys is
not considered by tcmpstruct.
Return Value tcmpstruct returns one of the return values above or an
error code if an error occurred.
Example #include <stdio.h>
#include "crde.h"
int tbuffers = 128;
int main()
{
TABLE *t1, *t2;
t1 = topen("sups");
t2 = tborrow(STATIC, t1);
switch (tcmpstruct(t1, t2)) {
case 0 :
printf("Tables are not compatible.\n");
break;
- 107 -
case 1 :
printf("Tables are compatible but with "
"different primary keys.\n");
break;
case 2 :
printf("Tables are identical.\n");
break;
default :
printf("Error %d occurred.\n", terrno);
}
}
----------------------------------------------------------------
tcols
----------------------------------------------------------------
Description Returns the number of columns in a table.
Declaration int tcols(TABLE *t);
Remarks tcols returns a value from 1 to 256.
Return Value tcols returns the number of columns in a table if
successful, or an error code if an error occurred.
See Also trows.
Example #include <stdio.h>
#include "crde.h"
int tbuffers = 128;
int main()
{
TABLE *t;
t = topen("sups");
printf("Sups table has %d columns.\n", tcols(t));
tclose(t);
}
----------------------------------------------------------------
tcolsize
----------------------------------------------------------------
Description Returns the size of a column.
Declaration int tcolsize(TABLE *t, char *c);
Remarks tcolsize returns a value from 1 to 256, the size of
the column in bytes.
- 108 -
Return Value tcolsize returns the size of a column if successful,
or an error code if an error occurred.
See Also tcoltype, trowsize.
Example #include "crde.h"
int tbuffers = 128;
int main()
{
TABLE *t;
t = topen("sups");
printf("Size of 'sname' column in Sups table is %d "
"bytes.\n", tcolsize(t, "sname"));
tclose(t);
}
----------------------------------------------------------------
tcoltype
----------------------------------------------------------------
Description Returns a column's type.
Declaration char tcoltype(TABLE *t, char *c);
Remarks tcoltype will return the character mnemonic for a
column's datatype (either c, i, l, d, f, or $). To
determine the size of a column, use tcolsize.
Return Value tcoltype returns the type of the column as a one
character code if successful, or 0 if an error occured.
See Also tcolsize.
Example #include <stdio.h>
#include "crde.h"
int tbuffers = 128;
int main()
{
TABLE *t;
t = topen("sups");
printf("Type of 'sname' column in Sups is ");
switch (tcoltype(t)) {
case 'c' : puts("char"); break;
case 'i' : puts("int"); break;
case 'l' : puts("long"); break;
- 109 -
case 'd' : puts("date"); break;
case 'f' : puts("double"); break;
case '$' : puts("dollar"); break;
}
tclose(t);
}
----------------------------------------------------------------
tcommit
----------------------------------------------------------------
Description Makes a transaction permenent.
Declaration int tcommit(TABLE *t);
Remarks tcommit makes a transaction on a table permanent by
saving all the changes made during the transaction to
disk.
tcommit will commit the transaction even if an error
occurs. In any event, the transaction is considered
over. To begin a new transaction you must call
ttransact again.
Calling tcommit outside a transaction has no effect
on a table.
Return Value tcommit returns 0 if successful, or an error code if
an error occurred.
See Also trollback, ttransact.
Example #include <stdio.h>
#include <stdlib.h>
#include "crde.h"
int tbuffers = 128;
int main()
{
TABLE *t;
t = topen("sups");
/* begin transaction */
ttransact(t);
MakeChanges(t);
printf("Save changes (y/n)? ");
if (toupper(getchar()) == 'Y')
tcommit(t);
- 110 -
else
trollback(t);
tclose(t);
}
----------------------------------------------------------------
tcopy
----------------------------------------------------------------
Description Copies a table.
Declaration TABLE *tcopy(char *name, TABLE *t);
Remarks tcopy creates a copy the table and its primary key.
Any secondary keys are not copied however.
If t is a TEMP table, it will automatically be
dropped by tcopy, even if an error occurs. Otherwise,
t is unaffected by tcopy.
Return Value tcopy returns a pointer to an open table if successful,
or an error code if an error occurred.
See Also tborrow.
Example #include <stdio.h>
#include "crde.h"
int tbuffers = 128;
int main(int argc, char *argv[])
{
/* program to make a backup of the table specified at
the command line; if backup name not supplied then
"backup" is used */
TABLE *t1, *t2;
char dest[64];
/* no argmuents, display help message */
if (argc < 2) {
printf("You must specify a table to backup.\n");
exit(1);
}
/* get destination name */
if (argc > 2)
strcpy(dest, argv[2]);
else
strcpy(dest, "backup");
- 111 -
/* open source and backup */
if ((t1 = topen(argv[1])) != NULL) {
if ((t2 = tcopy(dest, t1)) != NULL) {
tclose(t2);
printf("Backup to \"%s\" successful.\n", dest);
}
else
printf("Error occurred making backup "
"(error = %d).\n", terrno);
tclose(t1);
}
else
printf("Unable to open source file.\n");
}
----------------------------------------------------------------
tcount
----------------------------------------------------------------
Description Counts the number of values in a column.
Declaration int tcount(TABLE *t, char *c, void *result);
Remarks Because CRDE does not support NULL values, this
function is equivelent to calling trows. The
result for tcount is always long, regardless of
the column type.
result is undefined is an error occurs.
If t is a TEMP table, it will automatically be
dropped by tcount, even if an error occurs.
Otherwise, t is unaffected by tcount.
Return Value tcount returns 0 if successful, or an error code
if an error occurred.
See Also taverage, tmax, tmin, tsum.
Example #include "crde.h"
int tbuffers = 128;
int main()
{
TABLE *t;
long l;
int i;
t = topen("sups");
i = tcount(t, "s#", &l);
if (i < 0)
printf("Error %d occurred.\n", i);
- 112 -
else
printf("Table contains %ld suppliers.\n", l);
tclose(t);
}
----------------------------------------------------------------
tcreat
----------------------------------------------------------------
Description Creates a new table.
Declaration TABLE *tcreat(char *name, char *td);
Remarks tcreat creates a table named name, overwriting any
existing table by that name. The table's structure
is specified by the table descriptor td, an asciiz
(null- terminated) string containing a list of column
names and column types. Both column names and types
must be seperated by whitespace. Whitespace characters
are all those characters classified by the standard
library function isspace() plus the comma ','. All
other characters (including graphical symbols) are
significant. Case is also significant, so Cust# and
cust# are considered different. This is important since
every column name in a table must be unique.
An example tcreat call might look like
t = tcreat("sups", "*s# i, sname c26, city c26, status i");
Every column name must be followed by a datatype.
CRDE provides six basic datatypes common to virtually
all database managers. In addition, all datatypes are
analogous to native C datatypes which makes for easy
transition from CRDE tables to C variables.
Here is a list of the datatypes supported by CRDE each
followed by a description of the datatype, and its C
equivelent:
Type Description C equivelent
cXXX array of up to 256 char[ XXX]
characters. XXX is a
positive number
between 1 and 256.
i integer (whole int
numbers, + and -)
l long integer long int
d date 1date_ t
- 113 -
f floating-point double
$ dollar value 1dollar_t
1Date and dollar types have no direct C equivelents
but are represented as C types by the typedefs date_t
and dollar_t defined in CRDE. Typical representations
are long int for date_t and double for dollar_t
although you should always use the date_t and
dollar_t symbols for portability reasons.
Defining a primary key.
Any column name directly preceded with the '*'
character is considered to be part of the primary
key. There must be no whitespace between the '*' and
the column name. Up to 8 columns may be part of the
primary key. Although CRDE tables are not required
to have a primary key, it is generally good relational
practice to do so. Defining a primary key on a table
prevents two rows in a table which have identical values
in every column that is marked with an asterisk '*'. In
addition, defining a primary key may have performance
benefits as well (see Chapter 3, Creating Tables).
However there is a catch: a primary key makes a table
larger, using up more disk space.
Return Value tcreat returns a pointer to an open table if successful,
or NULL if an error occurs.
See Also topen.
Example: #include <stdio.h>
#include "crde.h"
typedef struct {
int no;
char name[26];
int age;
dollar_t salary;
} emp;
int tbuffers = 128;
int main()
{
TABLE *t;
emp e;
/* create a simple employee table */
t = tcreat("emps", "*e# i, name c26, age i, salary $");
- 114 -
/* add a record to the table */
e.no = 1;
strcpy(e.name, "John Smith");
e.age = 35;
e.salary = 33000.0;
tinsert(t, &e, 1);
/* close the table */
tclose(t);
}
----------------------------------------------------------------
tcrstruct
----------------------------------------------------------------
Description Creates a table from struct table.
Declaration TABLE *tcrstruct(char *name, TABLE *t);
Remarks Instead of using a table descriptor to create a new
table, tcrstruct uses a table containing column name
and type information similar to the one generated by
tstruct.
The only requirements which tcrstruct makes of the
struct table t is that its first two columns be of
type c (char). The first column should hold the
column names while the second should hold the column
type. Other than that, there are no other restrictions
on the structure of t.
The columns in the new table will appear in the same
order that they were physically inserted in the struct
table.
tcrstruct if useful in that it can be used by programs
to create table structures dynamically or even
interactively.
If t is a TEMP table, it will be automatically dropped
by tcrstruct, even if an error occurs. Otherwise, t is
unaffected by tcrstruct.
Return Value tcrstruct returns a pointer to an open table if
successful, or NULL if an error occurred.
Example #include <stdio.h>
#include "crde.h"
int tbuffers = 128;
- 115 -
int main()
{
TABLE *t, *struc;
char name[64], cname[32], ctype[5];
struc = tcreat(STATIC, "*colname c18, coltype c5");
if (struc == NULL) {
printf("Unable to create struct table.\n");
exit(1);
}
printf("Table name: ");
gets(name);
while (1) {
printf("\nColumn name: ");
gets(cname);
if (strcmp(cname, "") == 0)
break;
printf("Column type: ");
gets(ctype);
tload(struc, cname, ctype);
}
t = tcrstruct(name, struc);
if (t == NULL) {
printf("Error: unable to create new table.\n");
exit(1);
}
printf("\nTable \"%s\" created.\n", name);
printf("Table has %d columns totalling %d bytes in "
"size.\n", tcols(t), trowsize(t));
if (tkeyed(t))
printf("Table is keyed.\n");
else
printf("Table is not keyed.\n");
tclose(t);
tdrop(struc);
}
Output: Table name: dummy
Column name: *cust#
Column type: i
Column name: name
Column type: c26
Column name: phone
Column type: c13
Column name:
- 116 -
Table "dummy" created.
Table has 3 columns totalling 41 bytes in size.
Table is keyed.
----------------------------------------------------------------
tdelete
----------------------------------------------------------------
Description Deletes records from a table.
Declaration long tdelete(TABLE *t, ...);
Remarks tdelete expects a series of arguments which form a
search expression like the one described in tselect.
Any records matching the search expression are deleted
from the table.
Note: tdelete does not physically delete rows from
a table. Rather it marks the space as unused, and is
reclaimed automatically when you re-insert rows into
the table.
Return Value tdelete returns the number of rows deleted if successful,
or an error code if an error occurred.
See Also tdeleteif, tinsert, tselect.
Example int DeleteCust(int supno)
{
/* delete a supplier from the sups table; deletes any
orders made by that supplier to maintain referential
integrity */
int i;
i = tdelete(sups, "s#", EQ, supno, NULL);
if (i > 0)
i = tdelete(orders, "s#", EQ, supno, NULL);
return i;
}
----------------------------------------------------------------
tdeleteif
----------------------------------------------------------------
Description Deletes records from a table.
Declaration long tdeleteif(TABLE *t, int (*action)(void *), ...);
- 117 -
Remarks tdeleteif is exactly like tdelete except it accepts an
additional parameter action. Every row which matches
the search expression is passed to action for further
evaluation. action should behave exactly as the action
described in tselectif:
Result of action What happens
> 0 Row is deleted.
= 0 Row is not deleted.
< 0 An error occurred.
tdeleteif is aborted and
returns the result of action.
See tselectif for more details on action.
Return Value tdeleteif returns the number of rows deleted if
successful, or an error code if and error occurred.
See Also tdelete, tselect, tselectif.
Example /* routine to delete sups who live in either Atlanta
or Boston */
int DeleteSups(void *rec)
{
return (strcmp(((sup *)rec)->city, "Boston) == 0 ||
strcmp(((sup *)rec)->city, "Atlanta") == 0;
}
tdeleteif(sups, DeleteSups, NULL);
----------------------------------------------------------------
tdiff
----------------------------------------------------------------
Description Returns the relational difference of two tables.
Declaration TABLE *tdiff(char *name, TABLE *t1, TABLE *t2);
Remarks tdiff returns the relational difference of tables
t1 and t2. The result table will have a structure
identical to t1 and contain all the records found
in t1 not found in t2.
tdiff requires that t1 and t2 be compatible, i.e.
that they be equivelent on a column by column basis
in both type and size. However they may have
different primary or secondary keys. tdiff works
differently depending upon whether t1 is keyed or
not. If t1 is keyed then tdiff will return every
- 118 -
row in t1 whose key values does not match the key
values of any row in t2. If t1 is not keyed, then
only those rows which do not entirely match any row
in t2 will be returned.
If t1 or t2 is a TEMP table, then tdiff will drop
either or both of them, respectively. Otherwise, t1
and t2 are unaffected by tdiff.
Return Value tdiff returns a pointer to an open table if successful,
or NULL if an error occurred.
See Also tintersect.
Example /* return a list of all suppliers who have not made
any orders */
answer = tdiff(STATIC,
tproject(TEMP, sups, "s#", NULL),
tproject(TEMP, orders, "*s#", NULL)
);
----------------------------------------------------------------
tdrop
----------------------------------------------------------------
Description Closes and deletes a table.
Declaration int tdrop(TABLE *t);
Remarks tdrop closes and subsequently deletes a table and all
its indexes on disk. All memory held by the table is
released. Because the table is being deleted, any
unwritten buffers are simply released. If the table
was in a transaction, it is rolled back, before the
table is dropped.
You can also drop temporary tables (TEMP and STATIC.)
Dropping and closing a temporary table are functionally
equivelent.
tdrop drops the table, even if an error occurs.
Return Value tdrop returns 0 if successful, or an error code if an
error occurred.
See Also tclose.
Example #include <stdio.h>
#include "crde.h"
int tbuffers = 128;
- 119 -
int main()
{
TABLE *t, *t1;
/* "Packs" the sups table */
t = topen("sups");
if ((t1 = tcopy("dummy")) != NULL) {
tdrop(t);
tclose(t1);
trename("dummy", "sups");
}
else
tclose(t);
}
----------------------------------------------------------------
tdropindex
----------------------------------------------------------------
Description Drops a secondary index from a table.
Declaration int tdropindex(TABLE *t, char *id);
Remarks tdropindex removes a secondary index from a table.
td is an index descriptor like the one described in
tindex. The index described must exist to be dropped.
tdropindex works only with secondary indexes--you
cannot drop a primary index.
Return Value tdropindex return 0 if successful, or an error code
if an error occured.
See Also tindex.
Example #include "crde.h"
int main()
{
TABLE *t;
t = topen("sups");
tdropindex(t, "sname");
tclose(t);
}
- 120 -
----------------------------------------------------------------
tempty
----------------------------------------------------------------
Description Removes all rows from a table.
Declaration int tempty(TABLE *t);
Remarks tempty removes all rows from the table.
tempty is superior to tdelete for deleting all rows
from a table for a couple of reasons. tempty
physically reclaims all deleted space from the table.
In addition, tempty is much faster than tdelete.
Return Value tempty returns 0 if successful, or an error code if
an error occured.
See Also tdelete.
----------------------------------------------------------------
terase
----------------------------------------------------------------
Description Erases a table.
Declaration int terase(char *name);
Remarks terase erases the named table and all associated
indexes.
Never erase an open table, use tdrop instead.
Return Value terase returns 0 if successful or -1 if an error
occured.
See Also tdrop.
----------------------------------------------------------------
texportascii
----------------------------------------------------------------
Description Exports a table to an ascii file.
Declaration long texportascii(TABLE *t, char *dest);
Remarks texportascii exports a CRDE table to an ascii file.
t is the table to be exported. dest is the filename
of the ascii file to export to. dest is automatically
overwritten by texportascii.
- 121 -
texportascii writes columns to the ascii file in
the order in which they appear in t. A comma
seperates each column in the record, and each record
appears on an individual line of the ascii file.
Each column is output in a format according to its
datatype:
CRDE
type Format of column in ascii file
c CRDE will output a character string enclosed
in quotes "".
i CRDE will output a base 10 integer. Negative
numbers will be preceded with a negative sign -.
l CRDE will output a base 10 integer. Negative
numbers will be preceded with a negative sign -.
d Dates are output in the format mm/dd/yyyy and
enclosed in quotes.
f Floating point numbers are output with 4 decimal
places. Negative numbers are preceded with a
negative sign.
$ Dollar values are output with 2 decimal places.
Negative numbers are preceded with a negative
sign.
Return Value texportascii returs the number of rows exported if
successful, or an error code if an error occurred.
See Also timportascii.
Example: #include <stdio.h>
#include "crde.h"
int tbuffers = 128;
int main()
{
TABLE *t;
long result;
t = topen("sups");
result = texportascii(t, "sups.txt");
if (result < 0)
printf("Export failed (error code %d "
"occurred.\n", terrno);
else
printf("Export successful. %ld rows "
"exported.\n", result);
tclose(t);
}
- 122 -
----------------------------------------------------------------
texportdBASE
----------------------------------------------------------------
Description Exports a table to dBASE III Plus format.
Desclaration long texportdBASE(TABLE *t, char *dest);
Remarks texportdBASE exports a CRDE table to a dBASE III
Plus file named dest. texportdBASE does not assume
the ".DBF" extension so you must add this on your
own.
texportdBASE only exports the actual table. No
indexes, primary or secondary, are exported.
texportdBASE translates CRDE columns into equivelent
dBASE fields. Column names are converted to
uppercase and placed in the dBASE header in the same
order they appear in the CRDE table. Columns are
converted into the smallest dBASE type which can
accurately hold the data.
CRDE
type How CRDE column is converted to dBASE.
c Character field with a length equaling the
length of the c column - 1.
i Numeric field with a length of 6 and 0 decimal
places.
l Numeric field with a length of 11 and 0 decimal
places.
d CRDE dates are converted to dBASE format.
f Numeric field with a length of 19 and 4 decimal
places.
$ Numeric field with a length of 19 and 2 decimal
places.
Return Value texportdBASE returns the number of rows exported or an
error code if an error occurred.
See Also timportdBASE.
- 123 -
----------------------------------------------------------------
tflush
----------------------------------------------------------------
Description Flushes a table's buffers.
Declaration int tflush(TABLE *t);
Remarks tflush flushes any unwritten buffers to disk.
How tflush responds depends on the particular mode a
table is in:
T_NORMAL tflush will have no effect since CRDE
automatically flushes all changes after
every function call in normal mode.
T_WRITEC All unwritten buffers are flushed to
disk.
T_STATIC All unwritten buffers are flushed to
disk. If the temporary table does not
yet have an image on disk, tflush will
force the creation of one automatically.
T_TEMP Same as T_STATIC.
Return Value tflush returns 0 if succesful, of an error code if an
error occurred.
See Also twritec, tnormal, trelease.
----------------------------------------------------------------
tget
----------------------------------------------------------------
Description Reads rows from a table into an array.
Declaration int tget(TABLE *t, void *array, int n, ...);
Remarks tget reads up to n rows from t and copies them into
the buffer pointed to by array. The buffer should
be an array of structures similar to the structure
of t and should be large enough to hold at least n
rows.
tget expects a series of arguments which form a search
expression like the one described in tselect. Only
those records which match the search criteria are
placed in the buffer. tget stops after the entire
table has been scanned or n records are retreived,
whichever occurs first. The order in which the
records are retreived is undefined unless there no
- 124 -
search expression and the physical order of the records
in the table is known (for example, after a tsort
function call.)
Note that the rows retreived are only copies of the
records in the table. Rows are not removed from the
table by tget, nor will changing the rows retreived
in the buffer have any effect on the rows actually in
t.
Return Value tget returns the number of rows retreived if successful,
or an error code if an error occurred.
See Also tgetif, tselect.
Example: /* load some suppliers into a buffer */
sup buf[50];
sups = topen("sups");
tget(sups, buf, 50, "city", EQ, "Atlanta", NULL);
----------------------------------------------------------------
tgetif
----------------------------------------------------------------
Description Reads rows from a table into an array.
Declaration int tget(TABLE *t, void *array, int n,
int (*action)(void *), ...);
Remarks tgetif is exactly like tget except it accepts an
additional parameter action. Every row which matches
the search expression is passed to action for further
evaluation. action should behave exactly as the
action described in tselectif:
Result of action What happens
> 0 Row is retrieved.
= 0 Row is not retrieved.
< 0 An error occurred. tgetif is
aborted and returns the result
of action.
See tselectif for more details on action.
Return Value tget returns the number of rows retreived if
successful, or an error code if an error occurred.
See Also tget, tselect, tselectif.
- 125 -
----------------------------------------------------------------
tgroup
----------------------------------------------------------------
Description Groups a table on one or more columns.
Declaration TABLE *tgroup(char *name, TABLE *t, char *groupby, char
*td, int (*summary) (void *in, void *out, int first);
Remarks tgroup is an extremely powerful function which organzies
a table into groups and allows you to perform
calculations upon those groups by writing a special
grouping function.
tgroup creates a table name from the source table t.
groupby is a column list containing the columns to
group on. For example, "city" would group on the city
column, while "city,status" would group on both the
city and status columns. Up to 8 columns may be
specified in groupby. td is a table descriptor which
describes the output table. It is up to the group
function summary to generate a table which corresponds
to td.
summary is a user-defined function which creates the
result table. It accepts 3 parameters in (void *),
out (void *), and first (int), and must return an int.
in points to the next record in t being passed to summary.
out points to the record currently being generated by
summary. It has the same structure as described in td.
first determines the position of the row (pointed to by
in) within that particular group. If first is 1 then
the row is the first in that group, or 0 if it is not.
After every row in the group has been processed, tgroup
calls summary once more with first set to -1. This
allows summary to do any clean up or overall calculations
on the group.
Writing grouping functions.
A group function should have a declaration similar to
int groupfunc(void *in, void *out, int first);
Every row in each group is passed to groupfunc and is
referenced by in. The information in used to calculate
theoutput row for the group. out is a pointer to the
output row for a particular group. groupfunc is
responsible for building out. first is a parameter
used to determine whether a row is the first or last
element in the group. The information is used to
determine when to initialize the group and whether to
- 126 -
include the group in the answer table. The following
describes how tgroup uses groupfunc to generate the
answer table.
1. tgroup calls the groupfunc with first == 1. in
points to the first row in the group. The groupfunc
is expected to initialize out.
2. tgroup calls groupfunc with first == 0 for every
other row in the group. Each row is accessed through
the pointer in. groupfunc should update any
calculated columns in out based on each row in the
group.
3. After every row in the group has been passed to
groupfunc, tgroup calls groupfunc once more time
with first == -1. groupfunc should perform any final
calculations (now that all rows in the group have
been processed), and do any cleanup necessary. out
should now contain the values which will appear in
the answer table. However, groupfunc can also decide
whether or not it wants to include out in the answer
table. If groupfunc returns > 0 then tgroup will
include out. If groupfunc returns 0 then tgroup will
not include the row.
tgroup repeats step 1-3 for every group in the table.
In steps 1. and 2., groupfunc should return a value
>= 0 if no errors occurred. Regardless of the value
of first, if the groupfunc returns a value < 0, tgroup
aborts and returns NULL. terrno will be set to the
value returned by groupfunc. A well designed groupfunc
should include error-handling and perform any necessary
cleanup if an error occurs.
Complete details on writing group functions and full
examples are provided in Chapter 12, Performing Queries.
Return Value tgroup returns a pointer to an open table if successful,
or NULL if an error occurred.
Example /* Simply group function to count suppliers in each
city in the sups table */
int CountSups(void *in, void *out, int first)
{
typedef struct {
char city[26];
int count;
} gs;
switch (first) {
case 1:
strcpy(((gs *)out)->city, ((sup *)in)->city);
((gs *)out)->count = 0;
- 127 -
case 0:
((gs *)out)->count++;
break;
}
return 1;
}
answer = tgroup(STATIC, sups, "city", "city c26, count i",
CountSups);
----------------------------------------------------------------
timportascii
----------------------------------------------------------------
Description Imports an ascii file.
Declaration TABLE *timportascii(char *name, char *td, char *source);
Remarks: timportascii imports an ascii file into a CRDE table.
td is a table descriptor which describes the table.
source if the file name of the ascii file to import.
timportascii reads columns from the ascii file in the
order in which they appear in td. A comma should
seperate each column in the record, and each record
should appear on an individual row of the ascii file.
In addition, each column must conform to the following
format, dependent upon the datatype of the column
being imported:
CRDE
type Expected format of column in ascii file
c A character string contained in quotes "".
If the string is too large for the column, it
will be truncated to fit.
i A base 10 integer is expected. Negative values
should be preceded with a negative sign -. If
the integer is out of range for the column,
the value for that column is undefined.
l A base 10 integer is expected. Negative values
should be preceded with a negative sign -. If
the integer is out of range for the column, the
value for that column is undefined.
d A date in the format of mm/dd/yyyy is expected.
The date must be enclosed in quotes. Whitespace
is ignored.
- 128 -
f A floating point value with any number of decimal
places. Exponential notation may be used, +/-eNN.
Negative numbers must be preceded with a negative
sign -. If the number is out of range, the
resulting value imported is undefined.
$ Same as f.
Return Value timportascii returns a pointer to an open table if
successful, or NULL if an error occurred.
See Also texportascii.
Example #include "crde.h"
int tbuffers = 128;
int main()
{
TABLE *t;
/* Program to import the sups table from a pre-
existing ascii file. */
t = timportascii("sups", "*s# i, sname c26, city c26, "
"status i", "sups.dat");
tclose(t);
}
----------------------------------------------------------------
timportdBASE
----------------------------------------------------------------
Description Imports a dBASE III or dBASE III Plus data file.
Declaration TABLE *timportdBASE(char *name, char *source);
Remarks timportdBASE imports a dBASE III or dBASE III Plus
compatible data file. source is the name the the
dBASE file to import. timportdBASE does not assume
the ".DBF" extension so you must remember to add it
yourself.
timportdBASE only inports dBASE data files. It cannot
import dBASE indexes or memo files. timportdBASE
determines the CRDE table structure by reading the
dBASE file header and converting the fields to
CRDE equivelents.
- 129 -
dBASE
type How dBASE field is converted to CRDE
C c column equaling length of dBASE field + 1.
D dBASE's dates are converted to CRDE's date
format.
N If the number of decimal places equals 2 then
the colum type is $. If decimal places is > 0
then column is type f. If the length of the
number field is > 11 then the column is type f.
If the length of the number field is < 7 then
the column is type i. Otherwise the column is
type l.
L Logical fields are converted to type i. If the
field is 'Y' or 'T' the column will have a value
of 1, otherwise 0.
M CRDE does import memo fields. Any memo fields
in the dBASE file are ignored.
Return Value timportdBASE returns a pointer to an open table if
successful, or NULL if an error occurs.
See Also texportdBASE.
Example #include <stdio.h>
#include "crde.h"
int tbuffers = 128;
int main()
{
TABLE *t;
t = timportdBASE("Cust", "Cust.dbf");
if (t) {
printf("dBASE import successful. %ld rows"
" imported.\n");
tclose(t);
}
else
printf("Import failed (error code = %d).\n", terrno);
}
- 130 -
----------------------------------------------------------------
tindex
----------------------------------------------------------------
Description: Creates a secondary index.
Declaration: int tindex(TABLE *t, char *id);
Remarks: tindex creates a secondary index on a table specified
by the index descriptor id. id is an asciiz (null-
terminated) string of column names seperated by
whitespace. Whitespace consists of all characters
classified by the isspace() function plus the comma
','. id cannot contain more than 8 columns, nor can
a column occur more than once in the descriptor.
Descending keys.
You can mark any of the columns to be descending keys
by prepending the column name with an exclaimation
point '!'. Normally, the only time you would use
descending keys is in describing the sort order in a
tsort function. Although it is completely legal to
specify descending keys in normal indexes, it is
rarely useful to do so. Using either ascending or
descending keys in an index has no effect on
performance. CRDE handles both kinds of keys
transparently.
Once the secondary index is created, it is
automatically maintained by CRDE. It is opened and
closed whenever along with the table and updated
whenever records are inserted,changed, or deleted.
Unlike the primary index, a secondary index may contain
duplicates, so it cannot be used to prevent duplicate
keys in the table (only the primary index can do that.)
Secondary indexes are mostly used for speeding up
various CRDE operations. Since CRDE knows all about
your indexes, it can use them to help in searches,
sorting, joining, etc. to significantly improve
performance.
Calling tindex on an index which already exist rebuilds
that particular index. One drawback is that tindex
only works with secondary indexes, you can't rebuild
the primary index with tindex.
Return Value tindex returns 0 if successful, or an error code if an
error occurred.
See Also tdropindex.
- 131 -
Example #include "crde.h"
int tbuffers = 128;
int main()
{
TABLE *t;
/* create index on "sname" on Sups table. */
t = topen("sups");
tindex(t, "sname");
tclose(t);
}
----------------------------------------------------------------
tindexes
----------------------------------------------------------------
Description Calculates the number of secondary indexes.
Declaration int tindexes(TABLE *t);
Remarks tindexes returns the number of secondary indexes on a
table. The primary index is not counted.
Return Value tindexes returns the number of secondary indexes on a
table, or an error code if an error occurred.
See Also tkeyed.
----------------------------------------------------------------
tinsert
----------------------------------------------------------------
Description Insert record(s) into a table.
Declaration int tinsert(TABLE *t, void *recs, int n);
Remarks tinsert inserts n records into a table. The records
are contained in the structure, or array of structures,
pointed to by recs. The structure pointed to by recs
should exactly mimic the structure of the table. n
should not be larger than the number of rows in the
array.
If t is keyed, then only those records which do not
violate the primary key will be inserted.
Return Value tinsert returns the number of rows inserted if
successful or an error code if an error occurred.
- 132 -
See Also tload, treplace.
Example #include <stdio.h>
#include "crde.h"
int tbuffers = 128;
typedef struct {
char last[16], first[11], phone[9];
} person;
int main()
{
TABLE *t;
person p;
char c[10];
/* create a simple phone number list */
t = tcreat("phone", "last c16, first c11, phone c9");
twritec(t);
do {
printf("\nlast name: ");
gets(p.last);
printf("first name: ");
gets(p.first);
printf("phone no: ");
gets(p.phone);
tinsert(t, &p, 1);
printf("Enter another phone number (Y/N)?");
gets(c);
} while (toupper(*c) == 'Y');
tclose(t);
}
----------------------------------------------------------------
tintersect
----------------------------------------------------------------
Description Returns the intersection of two tables.
Declaration TABLE *tintersect(char *name, TABLE *t1, TABLE *t2);
Remarks tintersect returns the relational intersection of
tables t1 and t2. The result table will have a
structure identical to t1 and contain all the
records found in both t1 and t2.
- 133 -
tintersect requires that t1 and t2 be compatible,
i.e. that they have equivelent types and sizes on a
column by column basis. However they may have
different primary or secondary keys.
tintersect works differently depending upon whether
t1 is keyed or not. If t1 is keyed then tintersect
will return every record in t1 whos key values matches
the key values of any record in t2. If t1 is not keyed,
then only those records which entirely match a record
in t2 will be included.
If t1 or t2 is a TEMP table, then tintersect will drop
either or both of them, respectively. Otherwise, t1
and t2 are unaffected by tintersect.
Return Value tintersect returns a pointer to an open table if
successful, or NULL if an error occurred.
See Also tdiff, tunion.
Example /* query which uses tintersect to find those parts
ordered by both supplier #1 and supplier #2 */
TABLE *answer;
answer = tintersect(STATIC,
tproject(TEMP, orders, "*p#", "s#", EQ, 1, NULL),
tproject(TEMP, orders, "*p#", "s#", EQ, 2, NULL)
);
----------------------------------------------------------------
tjoin
----------------------------------------------------------------
Description Performs the relational join of two tables.
Declaration TABLE *tjoin(char *name, TABLE *t1, char *cl1, TABLE
*t2, char *cl2, char *pl);
Remarks tjoin joins tables t1 and t2 creating a new table.
The ability to join tables, relate information between
tables, is at the heart of relational philosophy.
Tables are related by "join" columns, tjoin works by
connecting all the rows in t1 whose join columns have
the same values as the corresponding join columns in t2.
cl1 and cl2 are column lists describing the join
columns for tables t1 and t2 respectively. cl1 and
cl2 must contain the same number of columns, and each
must be of identical types on a column by column basis.
Up to 256 join columns may be specified for each table.
- 134 -
pl describes the result table. pl is special column
list which may include a primary key by prepending any
columns with an asterisk '*'. In addition, pl may
contain columns from either t1 or t2. However, if t1
and t2 both contain a column of the same name, pl can
only include one of them, since by definition every
column name in a table must be unique. By default,
tjoin will chose the column in t1 over t2. You should
keep this in mind when designing tables that you may
plan on joining later.
If either t1 or t2 are TEMP tables, tjoin will drop
either or both of them, respectively. Otherwise, t1
and t2 are unaffected by tjoin.
Return Value tjoin returns a pointer to an open table if successful,
or NULL if an error occurred.
Example /* simple 2-table join containing all orders made by
supplier #100 */
TABLE *answer;
answer = tjoin(STATIC,
tselect(TEMP, sups, "s#", EQ, 100, NULL), "s#",
orders, "s#",
"s#,sname,city,status,p#,qty"
);
----------------------------------------------------------------
tkeyed
----------------------------------------------------------------
Description Determines whether a table is keyed or not.
Declaration int tkeyed(TABLE *t);
Remarks tkeyed returns 1 if t has a primary key, or 0 if not.
tkeyed(t) + tindexes(t) returns the total number of
indexes on a table.
Return Value tkeyed return 1 if t is keyed or 0 if not. An error
code is returned if an error occurred.
See Also tindexes.
Example #include <stdio.h>
#include "crde.h"
int tbuffers = 128;
- 135 -
int main()
{
TABLE *t;
t = topen("sups");
if (tkeyed(t))
printf("Sups table is keyed.\n");
else
printf("Sups table is not keyed.\n");
tclose(t);
}
----------------------------------------------------------------
tkeys
----------------------------------------------------------------
Description Describes the secondary indexes on a table.
Declaration TABLE *tkeys(char *name, TABLE *t);
Remarks tkeys is a complementary function to tstruct. It
creates a table describing a table's secondary keys.
tkeys creates a table equivelent to
tcreat(name, "ext c4, key c144")
and fills it with information about a table's
secondary keys. The ext column contains the filename
extension of the index being described and is of the
format "s[n]" where [n] is a digit from 0 to 7. The
key column contains the index descriptor which created
the index.
If t is a TEMP table, then it will be dropped
automatically by tkeys. Otherwise, t is unaffected by
tkeys.
Return Value tkeys returns a pointer to an open table if successful,
or NULL if an error occurred.
See Also tstruct.
Example: #include <stdio.h>
#include "crde.h"
int tbuffers = 128;
int main()
{
TABLE *t;
/* Program to display information on all the
secondary indexes on Sups */
- 136 -
t = topen("sups");
tview(tkeys(TEMP, t), NULL, NULL, 1, 1, 80, 25,
0x07, 0x07, 0x07, NULL);
tclose(t);
}
----------------------------------------------------------------
tload
----------------------------------------------------------------
Description Loads a record into a table column by column.
Declaration int tload(TABLE *t, ...);
Remarks tload expects a series of arguments, one for each
column of the table. Each argument must have the
exact same type as the corresponding column in t.
In the case of a char column, tload expects a char *
to string which may be of any length. tload will
automatically truncate the string to fit in the
appropriate column. The resulting row is inserted
into t.
tload is useful when filling tables directly by the
program without user input.
Return Value tload returns 0 on success, or an error code if an
error occurred.
See Also tinsert, treplace.
Example #include <stdio.h>
#include "crde.h"
int tbuffers = 128;
int main()
{
TABLE *t;
t = tcreat("customer", "*cust# , name c26, city c26,"
" state c3, zip c6, balance $");
tload(t, 1, /* cust# */
"Star Software", /* name */
"Anywhere", /* city */
"MI", /* state */
"48262", /* zip */
0.0); /* balance */
- 137 -
/* load some more records... */
tload(t, 2, "Kent's Warehouse", "Jonesville", "NY",
10079, 0.0);
tload(t, 3, "The Grocery Store", "Auburn", "GA",
72623, 0.0);
tclose(t);
}
----------------------------------------------------------------
tlookup
----------------------------------------------------------------
Description Lookup rows in a table.
Declaration int tlookup(TABLE *t, ...);
Remarks tlookup is used to lookup values in a table.
tlookup expects a series of arguments which form a
search expression like the one described in tselect.
tlookup will return 1 if it finds a record matching
the criteria, or 0 if it does not.
tlookup is useful for supporting lookup tables to
validate entries into a database. For example you
might enter a customer number into an order table,
then call tlookup to verify that that customer number
exists in the customer table.
tlookup is very similar to tscan. The primary
difference between the two is that tlookup stops
after it finds the first record matching the criteria,
while tscan attempts to find all records matching the
criteria. Subsequently, tlookup will generally be
much faster than tscan.
If t is a temporary table then it is automatically
dropped by tlookup, even if an error occurs. Otherwise,
t is unaffected by tlookup.
Return Value tlookup returns 1 if a matching record is found or 0
if not. It returns an error code if an error occurred.
See Also tlookupif, tselect.
Example #include <stdio.h>
#include "crde.h"
/* function to add an order to the order table - uses
table lookup to validate the order */
int AddOrder(order *o)
{
int i;
- 138 -
/* validate new order against customer lookup table
to insure o->custno is a valid customer. Returns 1
if successful. */
i = tlookup(cust, "cust#", EQ, o->custno, NULL);
if (i > 0)
i = tinsert(orders, o, 1);
return i;
}
----------------------------------------------------------------
tlookupif
----------------------------------------------------------------
Description Lookup rows in a table.
Declaration int tlookupif(TABLE *t, int (*action)(void *), ...);
Remarks tlookupif is exactly like tlookup except it accepts
an additional parameter action. Every row which
matches the search expression is passed to action
for further evaluation. action should behave exactly
as the action described in tselectif:
Result of action What happens
> 0 Row found. tlookupif returns 1.
= 0 Row not found. tlookupif keeps
looking.
< 0 An error occurred. tlookupif
aborts and returns the result
of action.
See tselectif for more details on action.
If t is a temporary table then it is automatically
dropped by tlookupif, even if an error occurs.
Otherwise, t is unaffected by tlookupif.
Return Value tlookupif returns 1 if a matching record is found or 0
if not. It returns an error code if an error occurred.
See Also tlookup, tselect, tselectif.
Example /* lookup function to determine if there are any red
or blue parts */
int RedOrBlue(void *rec)
{
return stricmp(((part *)rec)->color, "red") == 0 &&
stricmp(((part *)rec)->color, "blue") == 0;
}
- 139 -
if (tlookupif(parts, RedOrBlue, NULL) > 0) {
/* ... */
}
----------------------------------------------------------------
tmark
----------------------------------------------------------------
Description Determines whether a table is in a transaction.
Declaration int tmark(TABLE *t);
Remarks If t is in a transaction, tmark returns a value > 0.
If t is not in a transaction, tmark returns 0.
Return Value tmark returns > 0 if a transaction on the table is
pending, 0 otherwise. An error code if returned if t
is not a table.
See Also tcommit, trollback, ttransact.
Example #include <stdio.h>
#include "crde.h"
int tbuffers = 128;
int VerifyTrans(TABLE *t)
{
if (tmark(t))
printf("Table is in a transaction.\n");
else
printf("Table is not in a transaction.\n");
}
int main()
{
TABLE *t;
t = topen("sups");
VerifyTrans(t);
ttransact(t);
VerifyTrans(t);
trollback(t);
VerifyTrans(t);
tclose(t);
}
- 140 -
----------------------------------------------------------------
tmax
----------------------------------------------------------------
Description Calculates the maximum value in a column.
Declaration int tmax(TABLE *t, char *c, void *result);
Remarks tmax calculates the maximum value in column c and
places the result in result. tmax works with all
column types: c, i, l, d, f, $. The result type
is identical to the type of the named column.
result is undefined if an error occurs.
If t is a TEMP table then it is automatically dropped
by tmax, even if an error occurs. Otherwise t is
unaffected by tmax.
Return Value tmin returns 0 if successful or an error code if an
error occurred.
See Also taverage, tcount, tmin, tsum.
Example /* query to find all suppliers who have a status >=
the supplier with the highest status in Atlanta */
double d;
tmax(tselect(TEMP, sups, "city", EQ, "Atlanta", NULL),
"status", &d);
answer = tselect(STATIC, sups, "status", GE, (int)d, NULL);
----------------------------------------------------------------
tmin
----------------------------------------------------------------
Description Calculates the minimum value in a column.
Declaration int tmin(TABLE *t, char *c, void *result);
Remarks tmin calculates the minimum value in column c and
places the result in result. tmin works with all
column types: c, i, l, d, f, $. The result type
is identical to the type of the named column.
result is undefined if an error occurs.
If t is a TEMP table then it is automatically dropped
by tmin, even if an error occurs. Otherwise, t is
unaffected by tmin.
- 141 -
Return Value tmin returns 0 if successful or an error code if an
error occurred.
See Also taverage, tcount, tmax, tsum.
Example /* query to find all suppliers who have a status <=
the supplier with the lowest status in Atlanta */
double d;
tmin(tselect(TEMP, sups, "city", EQ, "Atlanta", NULL),
"status", &d);
answer = tselect(STATIC, sups, "status", LE, (int)d, NULL);
----------------------------------------------------------------
tmode
----------------------------------------------------------------
Description Determines table mode.
Declaration int tmode(TABLE *t);
Remarks tmode returns 1 of 4 constants describing the current
mode of the table:
T_NORMAL The table is in normal mode.
T_WRITEC The table is in write-caching mode.
T_TEMP The table is a TEMP temporary table.
T_STATIC The table is a STATIC temporary table.
A permanent table will return either T_NORMAL or
T_WRITEC. A temporary table will return either T_TEMP
or T_STATIC.
Return Value tmode returns one of the constants above, or an error
code if an error occurred.
See Also tnormal, twritec.
Example /* routine to display a table's current mode */
int PrintMode(TABLE *t)
{
printf("Table mode = ");
switch (tmode(t))
{
case T_NORMAL :
printf("NORMAL");
break;
- 142 -
case T_WRITEC :
printf("WRITEC");
break;
case T_STATIC :
printf("STATIC");
break;
case T_TEMP :
printf("TEMP");
break;
}
printf(".\n");
}
----------------------------------------------------------------
tnormal
----------------------------------------------------------------
Description Puts a table in normal mode.
Declaration int tnormal(TABLE *t);
Remarks tnormal is turns off the write-caching mode set by
twritec. Any changes will be immediately written to
disk after every CRDE function call. Although this
greatly improves data integrity, it can have dramatic
effects upon performance. twritec explains in more
detail how CRDE buffering works and when you should
switch between normal and writec modes.
By default, all tables begin in normal mode (except
temporary tables).
tnormal has no effect on temporary tables (both TEMP
and STATIC), tables in a transaction, or tables already
in normal mode.
Return Value tnormal returns 0 if successful, or an error code if
an error occurred.
See Also twritec.
Example #include <stdio.h>
#include "crde.h"
typedef struct {
int code;
char des[64];
} error_t;
TABLE *err;
void DisplayErr(void)
{
error_t e;
- 143 -
if (terrno < 0) {
if (tget(err, &e, 1, "code", EQ, terrno) > 0) {
printf("Error %d: %s.\n", e.code, e.des);
}
else {
printf("Error table fault.\n");
exit(1);
}
}
}
void LoadErr(void)
{
/* create table containing error codes and messages */
err = tcreat("error", "*code i, des c64");
twritec(err);
tload(err, -11, "Out of memory");
tload(err, -16, "Integrity check");
tload(err, -21, "Disk seek");
/* load rest of error codes here... */
tnormal(err);
}
int tbuffers = 128;
int main()
{
TABLE *t;
LoadErr();
t = topen("sups");
DisplayErr();
tview(t, NULL, NULL, 3, 3, 78, 23, 0x07, 0x07, 0x07,
NULL);
DisplayErr();
tall(tclose);
}
----------------------------------------------------------------
topen
----------------------------------------------------------------
Description Opens a table.
Declaration TABLE *topen(char *name);
- 144 -
Remarks topen opens the table specified by name and all
associated indexes. The table must already exist
to be opened.
topen does a number of integrity checks to insure
that the table and/or any indexes are not corrupt
topen will automatically attempt to repair any
corrupted indexes on the table. However, if the
table itself is corrupt, you will have to use trepair
to fix the table.
topen will fail if it fails to open any of the files
associated with the table.
Return Value topen returns a pointer to an open table is successful,
or NULL if an error occured.
See also tclose.
Example #include <stdio.h>
#include "crde.h"
int tbuffers = 128;
int main()
{
TABLE *t;
if ((t = topen("customer")) == NULL) {
printf("unable to open table.\n");
exit(1);
}
MainProgram();
}
----------------------------------------------------------------
tproject
----------------------------------------------------------------
Description Creates a relational projection of a table.
Declaration TABLE *tproject(char *name, TABLE *t, char *pl, ...);
Remarks tproject creates a projection of t. In relational
terms, a projection of a table is a table containing
one or more of the columns of the original. pl is a
column list which defines the projected table. All
the columns in pl must exist in t, although the
reverse is not necessarily true. In addition, the
columns may be arranged in any order desired.
- 145 -
You may also define a primary key on the projected
table, which may be different from the original. You
can use this technique to extract distinct values in
a column from a table. For example, suppose you
wanted a list of all distinct customer numbers in an
orders table. You might use the following query:
orders = topen("orders");
answer = tproject(TEMP, orders, "*cust#", NULL);
Adding the '*' in front of "cust#" removes any duplicate
customer numbers from the answer table.
tproject expects a series of arguments which form a
search expression like the one described in tselect.
With this you can project selected subsets of a table,
or the whole table itself.
If t is a temporary table then it is automatically
dropped by tproject, even if an error occurs. Otherwise,
t is unaffected by tproject.
Return Value tproject returns a pointer to an open table if
successful, or NULL if an error occurred.
See Also tprojectif, tselect.
Example #include "crde.h"
int main()
{
TABLE *t, *answer;
/* Program to display all cities where there are
suppliers, in alphabetical order */
t = topen(sups);
answer = tsort(STATIC,
tproject(TEMP, sups, "*city", NULL),
"city"
);
tview(answer, NULL, NULL, 3, 3, 78, 23, 0x07, 0x07,
0x07, NULL);
tall(tclose);
}
- 146 -
----------------------------------------------------------------
tprojectif
----------------------------------------------------------------
Description Creates a projection of a table.
Declaration TABLE *tprojectif(char *name, TABLE *t,
int (*action)(void *), ...);
Remarks tprojectif is exactly like tproject except it accepts
an additional parameter action. Every row which
matches the search expression is passed to action for
further evaluation. action should behave exactly as
the action described in tselectif:
Result of action What happens
> 0 Row is projected.
= 0 Row is not projected.
< 0 An error occurred. tprojectif
is aborted and returns NULL.
terrno is set to the result of
action.
See tselectif for more details on action.
If t is a TEMP table then it is automatically dropped
by tprojectif, even if an error occurs. Otherwise, t
is unaffected by tprojectif.
Return Value tprojectif returns a pointer to an open table if
successful, or NULL if an error occurred.
See Also tproject, tselect, tselectif.
Example /* query to list all parts which are red or blue */
int RedOrBlue(void *rec)
{
return stricmp(((part *)rec)->color, "red") == 0 &&
stricmp(((part *)rec)->color, "blue") == 0;
}
answer = tprojectif(STATIC, parts, "*pname", RedOrBlue,
NULL);
- 147 -
----------------------------------------------------------------
trelease
----------------------------------------------------------------
Description Release memory buffers.
Declaration int trelease(TABLE *t);
Remarks trelease releases any memory buffers held by the table.
Buffers which have not been written to disk (because of
twritec or ttransact) are not released. To release all
memory allocated to a table: end any transaction, flush
the table and then call trelease.
Since CRDE automatically manages memory between all
tables, it is never necessary to explicitly release a
table's memory to help speed another operation.
However, you can do so manually if you choose with
trelease.
tclose and tdrop both automatically release all memory
allocated to the table as part of their normal routine.
Return Value trelease returns 0 if successful or an error code if an
error occurred.
See Also tflush, tnormal, twritec.
----------------------------------------------------------------
trename
----------------------------------------------------------------
Description Renames a table.
Declaration int trename(char *oldname, char *newname);
Remarks trename renames a table and all associated indexes.
A table should be closed before it is renamed.
trename will fail if a table with the name new
already exists.
Return Value trename returns 0 if successful, or -1 if an error
occured.
See Also terase.
Example #include "crde.h"
int main()
{
/* renames "Sups" to "Supplier" */
trename("sups", "supplier");
}
- 148 -
----------------------------------------------------------------
trepair
----------------------------------------------------------------
Description Repairs a corrupted table.
Declaration TABLE *trepair(char *name, char *td);
Remarks trepair attempts to a repair a corrupted table by
using the table descriptor td as a model of what the
table should look like. It then trys to rebuild the
table using the infomation provided.
trepair makes all its assuptions based upon td. The
only assumption it makes about the table is that it
is corrupted. Thus, it is very important that td is
correct. If it isn't, trepair could do more harm than
good, most likely making the table totally unrecoverable.
trepair can fix logically corrupted tables,
specifically tables with corrupted headers, usually
without a hitch. trepair will attept to recover as
many rows from the atble as it can, but there is no
guarentee that all of them will be salvaged. trepair
can also fix certain types of physically damaged tables
as well. However, trepair is limited to the extent in
which it can fix physically damaged tables. It cannot
undelete tables, nor can it repair lost clusters or
sectors. Also, trepair must be able to open the file
to repair it.
trepair repairs only the table and primary index, if
one exists. Any secondary indexes must be rebuilt
manually. Simply call tindex to rebuild any secondary
indexes.
Return Value trepair returns a pointer to an open table if
successful, or NULL if an error occurred.
Example #include <stdio.h>
#include "crde.h"
int tbuffers = 128;
int main()
{
TABLE *t;
t = topen("sups");
if (!t) {
printf("Unable to open table, attempting to "
"repair...\n");
t = trepair("sups", "*s# i, sname c26, city c26, "
"status i");
- 149 -
if (!t) {
printf("Repair failed.\n");
exit(1);
}
else
printf("Repair successful. %ld rows "
"recovered.\n", trowsfound);
}
MainProgram();
}
----------------------------------------------------------------
treplace
----------------------------------------------------------------
Description Replace a row in a table.
Declaration long treplace(TABLE *t, void *recs, long n);
Remarks treplace is identical to tinsert except that if a
row with the same primary key already exists in the
table, it is replaced by the new row.
Return Value treplace returns the number of rows replaced if
successful, or an error code if an error occurred.
See Also tinsert.
Example #include "crde.h"
typedef struct {
int s;
char sname[26];
char city[26];
int status;
} sup;
sup buf[100];
int tbuffers = 128;
int main()
{
TABLE *t;
int i, j;
t = topen("sups");
i = tget(t, sups, 100, "city", EQ, "Atlanta", NULL);
DoOperationsOnBuf();
treplace(t, sups, i);
tclose(t);
}
- 150 -
----------------------------------------------------------------
trestruct
----------------------------------------------------------------
Description Restructures a table.
Declaration TABLE *trestruct(char *name, TABLE *t, char *td);
Remarks trestruct creates a restructured version of t.
trestruct is similar to tproject in that you can
remove columns, rearrange columns and redefine the
primary key. In addition you can also add new columns
and change the types of columns within a table.
td is a table descriptor like the one described in
tcreat. If the column listed in td is found in t
then one of two things happens: if the new column
is of the same type then data from the original column
is simply copied to the new column. If the types are
different, trestruct tries to convert the data to
the new type. The table listing valid type conversions
is listed below:
Type conversion table
c i l d f $
c x x x x x x
i x x x - x x
l x x x - x x
d x - - x - -
f x x x - x x
$ x x x - x x
[x] can be converted [-] cannot be converted
If a column listed in td not found in t all the values in
that column are initalized to some inital value (usually
0 or "").
Consider the following example:
t = tcreat("dummy", "*id i, date d, quant i, amount f");
r = trestruct("restruct", t,
"*id c6, " /* converted id from int to char */
"quant i," /* switched date and... */
"date d, " /* ...quant columns */
"salary $"); /* new column--will be set to 0.00*/
- 151 -
If t is a TEMP table then it is automatically dropped
by trestruct, even if an error occurs. Otherwise, t
is unaffected by trestruct.
Return Value trestruct returns a pointer to an open table if
successful, or NULL if an error occurred.
----------------------------------------------------------------
trollback
----------------------------------------------------------------
Description Rollback a transaction.
Declaration int trollback(TABLE *t);
Remarks trollback rolls back a transaction on a table
returning the table to the state it was in just prior
to beginning the transaction.
trollback rolls back the table even if an error occurs.
In either case, the transaction is considered over.
Calling trollback outside a transaction has no effect
on a table.
Return Value trollback returns 0 if successful or an error code if
an error occurred.
See Also tcommit, ttransact.
Example See tcommit.
----------------------------------------------------------------
trows
----------------------------------------------------------------
Description Calculates number of rows in a table.
Declaration long trows(TABLE *t);
Remarks trows returns a number between 0 and 2147483647.
Return Value trows returns the number of rows in the table or an
error code.
See Also tcols.
Example /* generic routine to display a table with tview;
table cannot be TEMP */
- 152 -
int ViewTable(TABLE *t)
{
gotoxy(3, 2);
printf("%ld rows found.", trows(t));
return tview(t, NULL, NULL, 3, 3, 78, 23, 0x07, 0x07,
0x07, NULL);
}
----------------------------------------------------------------
trowsize
----------------------------------------------------------------
Description Calculates size of a row/record in a table.
Declaration int trowsize(TABLE *t);
Remarks trowsize returns a value from 1 to 4000.
Return Value trowsize returns the size of a single row if
successful, or an error code if an error occurred.
See Also tcolsize.
Example #include <stdio.h>
#include "crde.h"
int tbuffers = 128;
int main()
{
TABLE *t;
t = topen("sups");
printf("Sups table row = %d bytes in size.\n",
trowsize(t));
tclose(t);
}
----------------------------------------------------------------
tscan
----------------------------------------------------------------
Description Scans a table.
Declaration long tscan(TABLE *t, ...);
Remarks tscan expects a series of arguments which form a search
expression like the one described in tselect.
- 153 -
tscan doesn't really do much except count the number
of rows which match the search expression. Generally,
tscan has little use in most applications.
Return Value tscan returns the number of rows scanned, or an error
code if an error occurred.
See Also tscanif, tselect.
Example #include <stdio.h>
#include "crde.h"
int tbuffers = 128;
int main()
{
TABLE *t;
long result;
t = topen("sups");
result = tscan(t, "city", EQ, "Atlanta", NULL);
printf("There are %ld suppliers in Atlanta.\n", result);
tclose(t);
}
----------------------------------------------------------------
tscanif
----------------------------------------------------------------
Description Scans a table.
Declaration long tscanif(TABLE *t, int (*action)(void *), ...);
Remarks tscanif is exactly like tscan except it accepts an
additional parameter action. Every row which matches
the search expression is passed to action for further
evaluation. action should behave exactly as the
action described in tselectif. However, tscanif, by
definition, doesn't really do anything in itself. It
simply goes through all the rows matching the search
expression and passes them to action. Thus, in this
case, it doesn't really matter whether action returns
0 or > 0.
Result of action What happens
> 0 Scanning continues.
= 0 Scanning continues.
< 0 An error occurred. tscanif
is aborted and returns NULL.
terrno is set to the result of
action.
- 154 -
See tselectif for more details on action.
tscanif can be used for reporting. Since action
receives a copy of every record scanned, simply design
an action function to print out the record.
Return Value tscanif returns the number of records scanned, or an
error code if an error occurred.
See Also tscan, tselect, tselectif.
Example See Chapter 12, Creating Reports.
----------------------------------------------------------------
tselect
----------------------------------------------------------------
Description Selects rows/records from a table.
Declaration TABLE *tselect(char *name, TABLE *t, ...);
Remarks tselect creates a table, identical in structure t,
containing records from t matching a specified search
criteria.
tselect expects a NULL terminated list of arguments
which formulate the search expression. A search
expression contains a number of conditions. Only
those records which match all the conditions in the
search expression are selected.
A typical tselect function call will have the following
format:
TABLE *tselect(name, t, colname1, relop1, value1,
colname2, relop2, value2, etc..., NULL);
Each condition is a set of exactly three arguments:
colname[n], relop[n], and value[n], described below...
colname[n] is any valid column name as an asciiz
string (char *).
relop[n] is a relational operator (int) defined in
crde.h. Valid relops are:
relop C Equivelent
EQ ==
LT <
GT >
- 155 -
LE,LTE <=
GE,GTE >=
NE,NEQ !=
value[n] is some value, either a constant or a
variable. The type of value[n] must be exactly the
same as the column type of colname[n]. In the case
of a char type, value[n] may be a char * pointing to
a string of any length.
An expression may contain no conditions, in whichcase,
every record is selected. Otherwise a row must match
every condition to be selected.
Here are some examples:
/* selects every record in table */
tselect(TEMP, t, NULL);
/* selects every record where cust# == 131 */
tselect(TEMP, t, "cust#", EQ, 131, NULL);
/* selects every record where cust# == 131 && age > 21 */
tselect(TEMP, t, "cust#", EQ, 131, "age", GT, 21, NULL);
/* "qty" in this case is of type long - note hold the
value must be cast as long */
tselect(TEMP, t, "qty", GT, 1000L, NULL);
/* select which uses variables */
int drinkage = 21;
...
tselect(TEMP, t, "name", EQ, "Mark", "age", GTE, drinkage,
NULL);
A search expression may not contain more than 16
conditions.
CRDE uses a powerful search engine to analyze and
perform searches. The search engine knows about a
table's columns and indexes and will automatically
determine the fastest way to perform the search. The
search engine will use any indexes that are available,
but can search the table sequentially as well. In
some cases it may opt for a sequential search, even
if an available index exists, if it thinks it can
perform the search faster that way.
It also makes no difference in what order an expression
is organized, the search engine always reorganizes an
expression to produce the fastest possible search.
- 156 -
Several other functions besides tselect utilize the
search engine to perform conditional operations on a
table. These "search related functions" (excluding
tselect) are: tproject, tdelete, tchange, tscan,
tlookup, and tget, respectively. In addition, each
search related function has a cousin function known as
"..if functions", which provide even more powerful
search capabilities including user-defined searches,
nested queries, correlated sub-queries, and more.
These are tselectif, tprojectif, tdeleteif, tchangeif,
tscanif, tlookupif, and tgetif. For more on the
capabilities of "..if functions" see tselectif.
If t is a TEMP table then it is dropped automatically
by tselect, even if an error occurs. Otherwise, t is
unaffected by tselect.
Return Value tselect returns a pointer to an open table if
successful, of NULL if an error occurred.
See Also tselectif, tproject, tdelete, tchange, tscan, tlookup,
tget.
----------------------------------------------------------------
tselectif
----------------------------------------------------------------
Description Selects rows/records from a table.
Declaration TABLE *tselectif(char *name, TABLE *t,
int (*action)(void *), ...);
Remarks tselectif is one of seven "..if functions" which
are identical to their regular counterparts in every
respect except one: each accepts an additional
parameter action.
action is a user defined function which futher
evaulates the row. Every row which passes the initial
search criteria is passed to action for further
evaluation.
action must return type int. The result of action
determines how the record should be processed:
Result of action Meaning
> 0 Select the row.
= 0 Do not select the row.
- 157 -
< 0 An error occurred. tselectif
aborts and returns NULL.
terrno is set to the result of
action.
action may modify the record passed to it without harm
(although this does have side effects when used in
tchangeif). action can perform queries on other
tables based on the information passed to it. action
can even perform queries on t, allowing for extremely
powerful and complex querying capabilities.
However, there is one restriction. action is
disallowed from altering t in any way. When any
"..if function" calls action, t is automatically placed
in a special read-only mode. Because of the complex
nature in which CRDE performs searches, t cannot be
changed during the course of a query. CRDE functions
which attempt to alter t will return a "table may not
be modified" error.
CRDE functions which can change a table are listed below:
tadd tchange tchangeif tchcol
tdelete tdeleteif tdropindex tempty
tindex tinsert treplace tload
tsubtract tupdate
In addition, you may not close, drop a table in read-
only mode. This is also the only case in which if t is
a TEMP table, it will not be automatically dropped by
any CRDE functions called in action.
As you can see, "..if" functions provide almost
unlimited querying capabilities. You might think the
these functions could replace their regular counterparts
because you can do everything a search expression can
do inside of action.
However, there are several reasons why not to:
- the most obvious is that "..if" function require you
to write a user-defined function for every query.
- CRDE can only take advantage your indexes if you
specify a search expression. For this reason you
should always put as much of the query in the search
expression as you possibly can.
- and since action must receive a copy of every record
for evaluation, "..if functions" are slightly slower
than their regular counterparts.
- 158 -
There are 7 "..if functions", each of which directly
corresponds to one of the 7 "search related functions".
All behave in a similar fashion as described above.
They are: tprojectif, tdeleteif, tchangeif, tscanif,
tlookupif, and tgetif, respectively.
If t is a TEMP table then it is automatically dropped
by tselectif, even if an error occurs. Otherwise, t is
unaffected by tselectif.
Return Value tselectif returns a pointer to an open table if
successful, or NULL if an error occurred.
See Also tselect, tprojectif, tdeleteif, tchangeif, tscanif
tlookupif, tgetif.
Example /* query to select all suppliers who have a status >=
the average status for suppliers from that city */
int GTAvgForCity(void *rec)
{
int i;
double d;
i = taverage(tselect(TEMP, ((sup *)rec)->status, &d);
if (i < 0)
return i;
return ((sup *)rec)->status >= (int)d;
}
answer = tselectif(STATIC, sups, GTAvgForCity, NULL);
----------------------------------------------------------------
tsort
----------------------------------------------------------------
Description Sorts a table.
Declaration TABLE *tsort(char *name, TABLE *t, char *id);
Remarks tsort returns a copy of a table in sorted order. The
result table is physically sorted according to the sort
order given by id. id is any valid index descriptor
(see tindex for a detailed description of index
descriptors). If t is a TEMP table then it is
automatically dropped by tsort, even if an error
occurs. Otherwise, t is unaffected by tsort.
Return Value tsort returns a pointer to an open table is successful,
or NULL if an error occurred.
See Also tindex.
- 159 -
Example #include <stdio.h>
#include "crde.h"
int tbuffers = 128;
int main()
{
TABLE *t;
/* Display all suppliers with a status <= 5 ordered
by supplier name */
t = topen("sups");
answer = tsort(STATIC,
tselect(TEMP, t, "status", LE, 5, NULL),
"sname"
);
tview(answer, NULL, NULL, 3, 3, 78, 23, 0x07, 0x07,
0x07, NULL);
tall(tclose);
}
----------------------------------------------------------------
tstruct
----------------------------------------------------------------
Description View the structure of a table.
Declaration TABLE *tstruct(char *name, TABLE *t);
Remarks tstruct returns the structure of a table as a table.
It is useful for looking at table's structure if you
forget the columns or primary key of a table. A
similar routine exists for examining the secondary
keys on a table: tkeys.
The format of the struct table is equivelent to
tcreat(name, "name c18, type c6");
The rows in the struct table represent the columns in
the table. The name column contains the column name
(preceded by an '*' if part of the primary key.) The
type column contains the type specifier as would be
specified in a table descriptor.
If t is a temporary table then it is automatically
dropped by tstruct, even if an error occurs.
Otherwise,t is unaffected by tstruct. In addition,
altering the struct table has no effect t.
- 160 -
Return Value tstruct returns a pointer to an open table if
successful, of NULL if an error occurred.
See Also tkeys.
Example #include <stdio.h>
#include "crde.h"
int tbuffers = 128;
int main()
{
TABLE *t;
/* command line progam to view the structure of any
table. */
if (argc < 2) {
printf("usage: tstruct <tablename>\n");
exit(1);
}
if ((t = topen(argv[1])) != NULL) {
tview(tstruct(TEMP, t), NULL, NULL, 3, 3, 78, 23,
0x07, 0x07, 0x07, NULL);
tclose(t);
}
else
printf("unable to open table.\n");
}
----------------------------------------------------------------
tsubtract
----------------------------------------------------------------
Description Removes records from one table which exist in another.
Declaration long tsubtract(TABLE *t1, TABLE *t2);
Remarks tsubtract removes records in one table which exist in
another. t1 and t2 must be compatible tables (see
tunion for a description of compatible tables). t2 is
unaffected by tsubtract.
How tsubtract works depends upon whether t1 is keyed
or not. If t1 is keyed then it will lose all rows
whose key values matches the key values of any record
in t2. If t1 is not keyed then it will lose any record
which exactly matches any record in t2.
If t2 is a TEMP table then it is automatically dropped
by tsubstract, even if an error occurs. Otherwise, t2
is unaffected by tsubtract.
- 161 -
Return Value tsubtract returns the number of rows subtracted if
successful, or an error code if an error occurred.
See Also tadd, tupdate.
----------------------------------------------------------------
tsum
----------------------------------------------------------------
Description Calculate the sum of a column.
Declaration int tsum(TABLE *t, char *c, void *result);
Remarks tsum calculates the sum of column c in a table and
places the result in result. The column must be
column must be of type i, l, f, or $. Other types
are ignored. The result is always assumed to be of
type double.
If t is a TEMP table then it is automatically dropped
by tsum, even if an error occurs. Otherwise, t is
unaffected by tsum.
Return Value tsum returns 0 if successful or an error code if an
error occurred.
See Also taverage, tcount, tmax, tmin, tmax.
Example #include <stdio.h>
#include "crde.h"
int tbuffers = 128;
int main()
{
TABLE *t;
double d;
/* display total number of parts ordered by a supplier */
t = topen("orders");
tsum(tselect(TEMP, t, "s#", EQ, 100, NULL), "qty", &d);
printf("Total number of parts ordered by supplier "
"#100 = %ld.\n", (long)d);
tclose(t);
}
- 162 -
----------------------------------------------------------------
ttransact
----------------------------------------------------------------
Description Begins a transaction.
Declaration int ttransact(TABLE *t);
Remarks ttransact begins a transaction on the table. The
table and all indexes are included in the transaction
so you may continue to use the table just as you
would normally.
Any changes made to the table are recorded in memory.
The transaction contines until a call to trollback,
tcommit, tclose, or tdrop is made. A call to tcommit
or tclose will save all the changes made to the table.
trollback or tdrop, on the other hand, discards the
changes, returning the table back to the state it was
in just prior to beginning the transaction.
ttransact automatically flushes all buffers in t before
starting the transaction to maximize integrity.
Because CRDE keeps all transactions in memory, you may
experience an -11 "Out of memory" error while performing
transactions on very large tables. If this occurs, you
should immediately rollback the transaction before doing
anything else. However, even in the event of an error,
trollback will be able to restore the original table.
Calling ttransact while in a transaction has no effect
on the table.
Return Value ttransact returns 0 if succesful, or an error code if
an error occurred.
See Also tcommit, trollback.
Example See tcommit.
----------------------------------------------------------------
tupdate
----------------------------------------------------------------
Description Update records in one table with those from another.
Declaration long tupdate(TABLE *t1, TABLE *t2);
Remarks tupdate is similar to tadd except that records with
identical primary keys are replaced by those in t2.
If t1 is an unkeyed table, tupdate behaves exactly
like tadd.
- 163 -
t1 and t2 must be compatible tables, i.e. identical
on a column by column basis in both type and size.
They may have different primary and secondary keys
however.
If t is a TEMP table then it is automatically dropped
by tupdate, even if an error occurs. Otherwise, t is
unaffected by tupdate.
Return Value tupdate returns the number of rows updated if successful,
or an error code if an error occurred.
See Also tadd, tsubtract.
Example #include <stdio.h>
#include "crde.h"
int tbuffers = 128;
int main()
{
TABLE *sups, *recv;
/* update the Sups table with an ascii file containing
updates received from the home office */
sups = topen("sups");
recv = timportascii(STATIC, "*s# i, sname c26, city "
"c26, status i", "recv.dat");
tupdate(sups, recv);
tclose(sups);
tclose(recv);
}
----------------------------------------------------------------
tunion
----------------------------------------------------------------
Description Returns the union of two tables.
Declaration TABLE *tunion(TABLE *t1, TABLE *t2);
Remarks tunion returns the relational union of tables t1
and t2. t1 and t2 must be compatible, i.e.
identical on a column by column basis in both type
and size. They do not have to have identical
primary or secondary keys.
The result of tunion is dependent upon whether t1
is keyed or not. tunion works by simply creating a
copy of t1 and subsequently adding to it the records
from t2. If t1 is keyed, only those records from
- 164 -
t2 which do not have a matching key in t1 will be
added to the result. If t1 is not keyed, the result
will contain all the records from t1 and t2.
tunion is useful for ORing queries together. For
example, suppose you wanted a list of all customers
whose age > 50 or salary < 5000.00. You could
construct the query as
cust = topen("customer");
answer = tunion(TEMP,
tselect(TEMP, cust, "age", GT, 50, NULL),
tselect(TEMP, cust, "salary", LT, 5000.00, NULL)
);
Note that this query will only work correctly if cust
has a primary key. Otherwise, the answer table may
contain duplicates (if the are any customers who are
both older then 50 and make less than 5000.00).
If t1 or t2 is a temporary table then either, or both,
are automatically dropped by tunion, even if an error
occurs. Otherwise, both t1 and t2 are unaffected by
tunion.
Return Value tunion returns a pointer to an open table is successful,
or NULL if an error occurred.
See Also tdiff, tintersect.
----------------------------------------------------------------
tview
----------------------------------------------------------------
Description Views a table.
Declaration int tview(TABLE *t, char *cl, char *id, int x1, int y1,
int x2, int y2, int cattr, int fattr, int battr,
void *rec);
Remarks tview allows you to integrate powerful browse
capabilities into your programs. With tview you can
view any table in any window on your screen, displaying
only those columns you want in the order that you want.
You can even use tview to select rows from a table.
cl Column list determining which columns to be
displayed and which order they are to be
displayed in. If cl is NULL, then all columns
are displayed in the order in which they were
originally defined in the table.
- 165 -
id Index descriptor which describes the sort order
the rows are to appear in. id may be any legal
index descriptor. If id is NULL, then the rows
are displayed in the order that they were
originally inserted into the table.
x1, y1, x2, y2
Coordinates of the window to display the view
in. May be any window in 1,1 to 80,25 but must
have a minimum height of 1 and a minimum width
of 5. tview automatically adjusts itself to
the size of the defined window, allowing you to
scroll both horizontally and vertically through
the table.
cattr text attribute of column values
fattr text attribute of the view frame
battr text attribute for background (in window)
rec When tview returns, a copy of the rows occupied
by the cursor is copied into the buffer pointed
to by rec. The buffer must be large enough to
hold the record. If rec is NULL, then the
record is not copied.
Cursor control in tview:
Up Go up a row.
Down Go down a row.
Left Go to the next column to the left.
Right Go to the next column to the right.
Ctrl-Left Go to the first column
Ctrl-Right Go to the last column
PgUp Go up a page of rows.
PgDn Go down a page of rows.
Home Go to the first row.
End Go to the last row.
Enter Exit returning decimal code 13.
Esc Exit returning decimal code 27.
If t is a TEMP table then it is automatically dropped
by tview, even if an error occurs. Otherwise t is
unaffected by tview.
tview adds about 10k to your programs.
Return Value tview returns 13 or 27 depending upon whether the
user exits by striking Enter or Esc respectively.
An error code is returned if an error occurred.
Example #include <stdio.h>
#include "crde.h"
int tbuffers = 128;
- 166 -
int main()
{
TABLE *t;
/* view Sups table with columns reversed */
t = topen("sups");
tview(t,"status,city,sname,s#", NULL, 3, 3, 78, 23,
0x07, 0x07, 0x07, NULL);
tclose(t);
}
----------------------------------------------------------------
twritec
----------------------------------------------------------------
Description Puts a table in write-cache mode.
Declaration int twritec(TABLE *t);
Remarks twritec causes CRDE to buffer as many changes to a
table as it can before flushing them to disk.
Normally, CRDE writes all changes immediately to
disk after every function call. However twritec
will prevent this, forcing CRDE to buffer as many
changes as possible in memory before writing them
to disk.
twritec is useful when making a bunch of changes at
once. For example, a program might call twritec
before inserting a number of records into a table,
and then call tnormal when the operation is complete.
Using this technique can have dramatic affects upon
performance.
On the other hand, doing something like:
twritec(t);
tdelete(t, "qty", LT 10, NULL);
tnormal(t);
won't provide any performance boost. Why?, becuase
all CRDE functions do internal buffering for the
duration of the command. twritec or tnormal only
affect the way CRDE handles buffering after the
function is completed.
You can flush unwritten buffers at any time with
tflush. During the course of a program's execution,
CRDE may flush them as well. In either case, CRDE
will continue to buffer changes until tnormal is
called, or the table is closed or dropped.
- 167 -
twritec has no effect on temporary tables, tables in
a transaction, or tables already in writec mode.
Return Value twritec return 0 if successful, or an error code if
an error occurred.
See Also tflush, tnormal.
Example #include <stdio.h>
#include "crde.h"
int tbuffers = 128;
int main()
{
TABLE *t;
t = tcreat("class", "*studentid i, last c16, init c2, "
"gpa f");
twritec(t); /* cache disk writes... */
/* load the table with some students */
tload(1522, "Adams", "D", 3.42);
tload(1627, "Burns", "C", 3.92);
tload(817 , "Bellamy", "J", 2.98);
/*
load rest of students here...
*/
tclose(t); /* unnecessary to call tnormal, tclose
automatically flushes buffers before
closing the table. */
}
----------------------------------------------------------------
year
----------------------------------------------------------------
Description Return the calendar year of a date_t value.
Declaration int year(date_t d);
Remarks year returns either 365 or 366 depending upon whether
the date is in a leap year or not.
Return Value year returns the calender year of the date, or -1 if
the date is invalid.
See Also day, month.
Example See day.
- 168 -
----------------------------------------------------------------
yearadd
----------------------------------------------------------------
Description Performs year addition/subtraction on a date.
Declaration date_t yearadd(date_t d, int years);
Remarks yearadd adds years years to d creating a new date.
yearadd knows about leap years and correctly performs
year addition so
yearadd(mkdate(1, 1, 1992), 1)
correctly yields 1/1/1993.
You can subtract years from the date by placing a
negative value for years.
Return Value yearadd returns a date_t value or -1 if the date is
invalid.
See Also monthadd.
- 169 -
Global Variables
----------------------------------------------------------------
tbuffers
----------------------------------------------------------------
Description Number of buffers to allocate to CRDE.
Declaration int tbuffers;
Remarks Every program which uses CRDE must declare this
variable globally in order for CRDE to run. CRDE
uses the value of this variable to allocate the
memory buffers it will use to perform its operations
dynamically at run time.
Each buffer is 512 bytes in size. CRDE requires an
absolute minimum of 64 buffers (32 k) to run. The
more buffers CRDE has available to it the faster it
will run. Normally a value of 128 will suffice for
most applications. However, programs which use large
tables, complex queries, and/or transaction tracking
may require 256, 512, or more buffers to run
effectively.
Example Normally tbuffers is delcared something like this:
int tbuffers = 128; /* standard min setting */
int main()
{
/* program here */
}
----------------------------------------------------------------
terrno
----------------------------------------------------------------
Description Holds error code of the last occurring error.
Declaration int terrno;
Remarks terrno holds the error code of the last error that
occurred. terrno is set whenever an error occurs,
but is never cleared by any CRDE function. However,
you may clear it if you wish. Thus, terrno is only
valid for the current function if the function itself
- 170 -
returns an error (either an error code, NULL, or
in rare cases 0, depending upon the function). If a
function returns an error code, then terrno will be
set to the same error code. If a function returns
NULL or 0 for an error you can use terrno to determine
the cause of the error.
Example #include <stdio.h>
#include "crde.h"
int tbuffers = 128;
int main()
{
TABLE *t;
t = topen("sups");
if (!t) {
printf("Unable to open table (error code = %d).\n",
terrno);
exit(1);
}
RestOfProgram();
tclose(t);
}
----------------------------------------------------------------
trowsfound
----------------------------------------------------------------
Description Number of rows involved in last operation.
Declaration long trowsfound;
Remarks trowsfound holds the number of records changed, deleted,
imported, etc., from the last operation. trowsfound is
set by most CRDE functions.
In the event of an error, trowsfound can be used to
detemine the number of rows processed before the error
occurred.
Example #include <stdio.h>
#include "crde.h"
int tbuffers = 128;
int main()
{
TABLE *t;
- 171 -
t = topen("sups");
result = texportascii(t, "sups.dat");
if (result < 0)
printf("Export failed (error code = %d) after "
"%ld rows.\n", terrno, trowsfound);
else
printf("Export successful. &ld rows exported.\n",
trowsfound);
tclose(t);
}
- 172 -
Appendix A
Error Codes
General Errors
-1 General Error
-1 is used as a general error code by CRDE. The actual meaning
of the error is completely context dependent. CRDE may also
generate a -1 code if it cannot determine the cause of an error.
That situation, however, is rare.
Resource Errors
-11 Out of Memory
This error occurs whenever there is not enough memory for CRDE
to complete an operation. Normally, this requirement is very
low. Because of the intelligent way in which it manages memory
CRDE can perform even the most sophisticated operations in
usually < 32k.
This error most commonly occurs when a large transaction has
eaten up most available memory. You should immediately
rollback the transaction before continuing.
-16 Integrity Check
CRDE tried to open a table which could not pass even the most
basic of integrity checks. It is very possible that the file
that was opened was not even a CRDE table. Otherwise, the table
has a corrupt header, at the very least. Use trepair to fix
the table.
Operating System Errors
-21 Disk Seek
A disk seek error occurred.
- 173 -
-22 Disk Read
A disk read error occurred.
-23 Disk Write
A disk write error occurred.
-24 File Create
CRDE was unable to create a file. This error will often
occur when too many file handles are already open.
-25 File Open
CRDE was unable to open a file. This error can occur if
too many file handles are open.
-26 File Close
An error occurred while CRDE was trying to close a table.
Internal Errors
-31 Internal Error
CRDE has detected an inconsistancy in its internal buffers.
This is usually an indication of some kind of memory corruption.
Your program may have accidentally written into the buffer
area by way of an unallocated pointer.
-32 Internal Error
Same as -31.
Key/Row Violations
-41 Key Exists
CRDE tried to insert a row into a keyed table for which a key
already exists. -41 is unique in that it does not specify an
error, rather an indication that the row was not inserted.
Unlike other errors, CRDE will continue to process a function
if an error -41 occurs.
- 174 -
-42 Key not found
CRDE was unable to find a matching key for a row in the table.
This is usually a sign of index corruption.
-51 Row previously deleted
CRDE tried to delete aa row which was already marked as deleted.
This is usually a sign of index corruption.
Parsing Errors
-101 Bad table name
A table name must either be TEMP, STATIC, as defined in crde.h,
or a legal DOS pathname <= 64 characters not containing wildcards
or an extension.
-102 Token too long
CRDE tried a parse a token which was too long for that particular
context.
-103 Empty column list
The column list, table descriptor, index descriptor, etc. must
contain at least one column name.
-104 Column list too long
There were too many columns listed in the column list for that
particular context.
-105 Syntax error
A general syntax error occurred.
Table Limit Errors
-111 Column too large
A column may not be more than 256 characters in length.
- 175 -
-112 Key too large
The combined total size of all columns in a key may not be
larger than 256 bytes.
-113 Row too large
The combined total size of all columns in a row may not be more
than 4000 bytes.
-114 Too many columns
A table may consist of a maximum of 256 columns. An index key
may consist of a maximum of 8 columns.
-115 Too many indexes
A table may not have more than 8 secondary indexes.
-116 No columns in this table
A table must consist of at least one column.
-117 Too many tables
CRDE imposes a restriction of no more than 128 open tables at
any one time.
Syntax Errors
-121 Illegal datatype
You specified an illegal datatype in a table descriptor. Legal
datatypes are c, i, l, d, f, and $.
-122 Unknown column
You specified a column in a table which CRDE could not recognize.
This error can often occur when you forget to end a search
expression/change list with a terminating NULL.
-123 Unknown index
You specified an index in a table which CRDE could not recognize.
- 176 -
-124 Duplicate column
A column name appears more than once in a column list.
-125 Duplicte key column
A key may not contain duplicate columns.
System Errors
-131 Invalid table
A TABLE * does not point to an open table.
-132 Table is not modifiable
You tried to modify a table which CRDE has placed in read-only
mode. CRDE does this whenever you call one of its "..if"
functions. The host table is placed in read-only mode until
the "..if" functions terminates.
-133 Operation restricted by transaction
Some CRDE functions are not permissible during a transaction.
You may not create or drop secondary indexes during a
transaction.
-134 Corrupt table
CRDE has determined that a table is corrupt. Use trepair to
fix the table.
-135 Duplicate tables
You tried to perform a two table operation on a single table.
This is not allowed.
-136 Corrupt index
CRDE has determined that one or more of a table's indexes are
corrupt. Rebuild the indexes.
-137 Table cannot be temporary
Use of a temporary table, TEMP or STATIC, is not permissable
in this context.
- 177 -
Search/Change Expression Errors
-141 Illegal relop
You specified an illegal relop in a search expression. Legal
relops defined in crde. h are EQ, GT, LT, GE, LE, NE.
-142 Expression too complex
A search expression may not contain more than 16 conditions.
-143 Too many changes
A change list may not contain more than 16 changes.
Miscelleneous Errors
-151 Join columns do not match
The join columns of two tables must be identical in both type
and size on a column by column basis.
-152 Incompatible structures
A two-table CRDE function requires that the tables have
compatible structure, i.e. they must be identicial in both
type and size on a column by column basis. The tables may
have different primary and secondary keys.
-153 Table is not tcrstruct compatible
The first two columns of a table passed to the tcrstruct
function must be c columns. tcrstruct imposes no other
restriction upon the table.
-154 Bad tview specs.
You specified a bad window for the tview function. A window
must be within the coordinates 1,1 to 80,25 and must have a
width of at least 5 and a height of at least 1.
-155 Unable to convert data
An import or export operation was unable to convert data
between CRDE and the foreign data type. This error may also
occur when trying to change a date column to something other
than a char column with trestruct.
- 178 -